Specific sheet access by user/password

[sampson32]

I have a workbook with 10 sheets.

Each sheet needs to be protected by a different password.

When a user logs in I would like them to be directed to a specific sheet and all other sheets hidden

I found some references to this on the forum but they had no code that I could try.

How could this be accomplished?

Thank you,

Sam

 

[ltijerina]

for clarification

Where "Sheet1" refers to the actual sheet name. Just change it to reflect your sheet name.

If my Sheet1 is "emp1" i should change Sheets("Sheet1") to
Sheets("emp1")?

If so, I tried that and still it is a no go. i get the same error message

 

[Smitty]

for clarification

Where "Sheet1" refers to the actual sheet name. Just change it to reflect your sheet name.

If my Sheet1 is "emp1" i should change Sheets("Sheet1") to
Sheets("emp1")?

If so, I tried that and still it is a no go. i get the same error message

Yes, but note that any leading or trailing spaces in the sheet name will throw off the code too. It has to be the exact name.

Smitty

 

[ltijerina]

dude you rock... i finally got it to work. i will add for the rest of the tabs.

now to figure out how to get all of the worksheets to open for the manager of the group. I will try to figure out the code listed above. if needed could i pick your brain again???

again, thank you for all your help.

 

[Smitty]

What I do for admin type tasks is test for user name.

Private Sub Workbook_Open()
    Dim AdminUser As String
        AdminUser = Environ("username")
        
        If AdminUser = "DefinedUserName" Then Exit Sub
        
        '   Password Code here
            
End Sub

And bail from the code if it's not you or your manager.

Smitty

 

[HalfAce]

I never knew there was a "how can I show all sheets" question added to this thread or I surely would've answered.

That can be done as simply as adding this to the previously posted code, right above the End Select line...

Case "Boss's Name"
        If pWord = "Boss's Password" Then
            For ws = 1 To WSCount
              Sheets(ws).Visible = True
            Next ws
        End If

This way it'll work no matter who's username is being used, as long as the user knows the boss's password. - ( )

Hope it helps.

[Hey Smitty. How everything? You guys get all moved in?]

 

[ltijerina]

Thanks HalfAce. the boss's code workes like a charm.

Pennysaver - hats off to you too.

I really appreciate all the help from you guys