How to display hidden worksheet based on password

How to display hidden worksheet based on password. A workbook has multiple worksheets. The administrator wishes to assign each worksheet to a specific co-worker who can view the hidden worksheet by logging in via a user-form. As soon as another user logs in, the earlier worksheet is hidden and the new user can view only his worksheet. Watch the training video to see how the complete process is automated with VBA:

Watch this video on YouTube.

Here’s the complete VBA code:

Private Sub DisplaySheet(mySheet As Integer)
Dim i As Integer
Unload Me
For i = 2 To Worksheets.Count

If i <> mySheet Then Worksheets(“Sheet” & i).Visible = False
If i = mySheet Then Worksheets(“Sheet” & i).Visible = True
Next i
Worksheets(“Reference”).Select
End Sub
Private Sub CommandButton1_Click()
Select Case Me.TextBox1.Value
Case “2”
DisplaySheet (2)
Case “3”
DisplaySheet (3)
Case “4”
DisplaySheet (4)
Case “5”
DisplaySheet (5)
Case “6”
DisplaySheet (6)
Case “7”
DisplaySheet (7)
Case Else
Me.Hide
Retry = MsgBox(“The Password is incorrect. Do you wish to try again?”, vbYesNo, “Retry?”)
Select Case Retry
Case Is = vbYes
Me.TextBox1.Value = “”
Me.TextBox1.SetFocus
Me.Show
Case Is = vbNo
Unload Me
Workbooks.Close
End Select
End Select
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
Cancel = True
End If
End Sub

Private Sub Workbook_Open()
UserForm1.Show

End Sub

Further reading:

Explaining  Private vs. Public Declarations

One thought on “How to display hidden worksheet based on password

  1. Baw

    Thank you for the tutorial, when I run the code I keep getting this msg ” subscript out of range” and highlights the displaysheet code. Any ideas?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *