I'm working on a multi-sheet workbook and I only want certain users to see certain sheets, but an admin to see all sheets. Each user has their own password assigned to them and can then see only the sheets designated for them.

My first issue is that when they entered their password at the prompt, the password actually displays instead of the secure black dots.

My second issue is that the hidden sheets ("User1, User2, User3, User4) are displaying until the user clicks Enable Content. I want the user to see only the sheet labeled "Instructions"; the admin can see all sheets. This tells me that the code to hide the worksheets isn't activated, so the user is seeing the hidden sheets while they attempt to log in.

How can I make this workbook more secure by hiding the password and the hidden sheets upon opening the workbook? Here's the code I'm using:

Private Sub Workbook_Open()
Dim ws As Worksheet, myPass As String, myWs
For Each ws In Worksheets
If ws.Name <> "Instructions" Then ws.Visible = 2
Next
myPass = LCase(InputBox("Enter password"))
If myPass = "TEST" Then
For Each ws In Sheets
ws.Visible = -1
Next
Else
myWs = Switch(myPass = "PASS", "Summary", _
myPass = "PASS1", "User1", _
myPass = "PASS2", "User2", _
myPass = "PASS3", "User3", _
myPass = "PASS4", "User4")
If IsNull(myWs) Then
MsgBox "Incorrect password; cannot display workbook.", vbInformation
Else
'Sheets("Overall Standings").Visible = -1
'Sheets("Weekly Standings").Visible = -1
Sheets("Week1").Visible = -1
Sheets(myWs).Visible = -1
Sheets(myWs).Activate
End If
End If
With Worksheets("Instructions")
.Activate
.Range("A1").Select
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Instructions").Activate
End Sub