11 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?

   1. You will get this error if your Workbook Sheets are not named as:
      Sheet1, Sheet2, Sheet3… Sheet7

      Because the DisplaySheet() code loops through the Sheet names appending the count (i) to the end.

      Hope this helps someone.

2. How to display hidden worksheet based on passwordx

3. 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

4. Pingback: Automatically Display Worksheets of Specific User | Excel VBA Training Online

5. 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

6. 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….

   1. How did you get this to work? I have similar problems as described above. Is there anyway you could share a workbook with multiple worksheets (and separate passwords) working?

   2. Hello Tom,

      That is true.
      You would need something like:

      Private Sub Workbook_SheetActivate(ByVal Sh As Object)
      If Sh.Name “Reference” Then
      If Sh.Name ActiveWorkbook.CustomDocumentProperties(“auth”).Value Then
      Sh.Visible = False
      MsgBox “You don’t have authorization to view that sheet!”
      End If
      End If
      End Sub

      This of course means you have to introduce a set document property section. See reference here: https://excel.tips.net/T001952_Protecting_Individual_Worksheets_by_User.html

7. I’m getting the following error message. What should be done?

   Private Sub CommandButton1_Click()

Comments are closed.