VBA Hiding worksheets, un-hidding them with passwords

[deadfishsurfer]

Hi
I don't know if its possible, but what i am trying to do is give different people different passwords for a work book.

So;
Person 1 - would see sheet 1 and sheet 2
Person 2 - would see sheet 1 and sheet 3
Person 3 - would see sheet 1 and sheet 4

The idea behind this is sheet 1 would act as a dashboard for accumulated data that everyone can see but they would only be able to see and edit the data on their allocated sheet and not be able to peek at the other sheets.

I've tried doing it per sheet which is fine but because you need to see the sheet to unlock it it causes a problem that you can still see that data for other users, which i am trying to prevent.

Does anyone have any ideas?

Many thanks

 

[mumps]

Copy and paste this macro into the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, doubleclick 'ThisWorkbook'. In the empty window that opens up, copy and paste the following macro.

Private Sub workbook_open()
    Application.ScreenUpdating = False
    Call HideSheets
    Dim password1 As String
    password1 = "Person1"
    Dim password2 As String
    password2 = "Person2"
    Dim password3 As String
    password3 = "Person3"
    Dim password4 As String
    password4 = "Person4"
    Dim response As String
    response = InputBox("Please enter your password.")
    If response = "" Then
        MsgBox ("You have not entered a password.  The workbook will now close.")
        ActiveWorkbook.Close False
    ElseIf response = "Person1" Then
        Sheets(2).Visible = True
        Exit Sub
    ElseIf response = "Person2" Then
        Sheets(3).Visible = True
        Exit Sub
    ElseIf response = "Person3" Then
        Sheets(4).Visible = True
        Exit Sub
    ElseIf response = "Person4" Then
        Sheets(5).Visible = True
        Exit Sub
    Else
        MsgBox ("You have not entered the correct password.  The workbook will now close.")
        ActiveWorkbook.Close False
    End If
    Application.ScreenUpdating = True
End Sub

In the code, you will see passwords setup as "Person1", "Person2". etc. Change these to suit your needs. Add any additional passwords. Click 'Insert' in the menu at the top and then click 'Module'. Copy and paste the following macro into the empty window that opens up.

Sub HideSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> "Sheet1" Then ws.Visible = xlSheetVeryHidden
    Next ws
    Application.ScreenUpdating = True
End Sub

Close the window to return to your sheet. Save the file as a macro-enabled file and then close it. Each time that you re-open the file you will be prompted to enter a password. The appropriate sheet will be visible depending on the password entered.

 

[deadfishsurfer]

Thanks for this
Exactly what I was looking for, it was the Elseif I was struggling with

Thank you again

 

[mumps]

You are very welcome.