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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

7 thoughts on “How to display hidden worksheet based on password”

  1. 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?

  2. Hello Dinesh

    Will you be able to share the work book ‘How to display hidden worksheet based on password’

    Thanks

    1. I’m trying to create the same workbook with the same code. I will then alter the passwords and information on the sheets.

      I copied the coding directly out of the Excel Video and am receiving: Run-time error ‘9’: Subscript out of range

      In the following coding:
      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

      The following is being highlighted in yellow “Worksheets(“Sheet” & i).Visible = False”

      I have 7 sheets. See the whole coding that I have entered below:
      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
      ‘Entering the Password – it is case-sensitive
      Case “SBR001”
      DisplaySheet (1)
      Case “4HN001”
      DisplaySheet (2)
      Case “REA002”
      DisplaySheet (3)
      Case “MIF001”
      DisplaySheet (4)
      Case “MEA001”
      DisplaySheet (5)
      Case “Reference”
      DisplaySheet (6)
      Case “Validate”
      DisplaySheet (7)
      Case Else
      Me.Hide
      Retry = MsgBox(“The password is incorrect. Do you want 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 CommandButton1_QueryClose(Cancel As Integer, CloseMode As Integer)
      If CloseMode = 0 Then
      Cancel = True
      End If
      End Sub

  3. Hi

    Thanks for this, can you help

    I copied the coding directly out of the Excel Video and am receiving: Run-time error ‘9’: Subscript out of range

    on If i = mySheet Then Worksheets(“Sheet” & i).Visible = True

  4. Hi

    I got this to work, however there is a problem because all a user has to do is rightclick and unhide the other tabs when they are in the document….

Leave a Reply

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