comboboxs in a userform handling a protected workbook and worksheets

I have created a userform1 with two dependant combo boxes, the worksheets and workbook is protected and I am struggling to place the unprotect function in the correct place. The purpose is to select from combo box one then select from combo box two; the value in combo box two is used by a command button on the user form to open the desired worksheet. Here lies the problem when everything is protected.
Location is combo box one and sheet name is combo box two.

The code functions without protection on.

Private Sub ComboBox1_Change()
    ThisWorkbook.Unprotect Password:="password"
Dim index As Integer
index = ComboBox1.ListIndex
Me.ComboBox2.Clear

Select Case index
    Case Is = 0
         With ComboBox2
             .AddItem "sheet one"
         End With
         
    Case Is = 1
         With ComboBox2
             .AddItem "sheet two"
             .AddItem "sheet three"
         End With
         
    Case Is = 2
         With ComboBox2
             .AddItem "sheet four"
             .AddItem "sheet five"
         End With
         
    Case Is = 3
         With ComboBox2
             .AddItem "sheet six"
         End With
         
     Case Is = 4
         With ComboBox2
             .AddItem "sheet seven"
             .AddItem "sheet eight"
             .AddItem "sheet nine"
         End With
End Select
End Sub

Private Sub CommandButton1_Click()
    ThisWorkbook.Unprotect Password:="password"
      Dim actWsh As String
    If ComboBox1.Value = "" Or _
       ComboBox2.Value = "" Then
        MsgBox "Both comboboxes must have a selection."
        Exit Sub
    Else
        MsgBox "OK to proceed."
        actWsh = ComboBox2.Text
        Worksheets(actWsh).Select
    Unload UserForm1
    End If
    ThisWorkbook.Protect Password:="password"
End Sub


Private Sub UserForm_Initialize()
    ThisWorkbook.Unprotect Password:="password"
With ComboBox1
     .AddItem "Location A"
     .AddItem "Location B"
     .AddItem "Location C"
     .AddItem "Location D"
     .AddItem "Location E"
End With
End Sub

The issue is relating to "hidden" and not password related. The code is fully functional for both protected and unprotected workbook and worksheets.
The problem relates to [code] actWsh = ComboBox2.Text Worksheets(actWsh).Select [code] obviously the worksheet has to be unhidden for actWsh (active worksheet) to function.
I believe I've uploaded a representative file. password = password

Hi there,

See if the attached version of your workbook does what you need. It uses the following code:

Option Explicit


'=========================================================================================
'=========================================================================================


Private Sub UserForm_Initialize()

    With Me.ComboBox1

        .AddItem "Northampton"
        .AddItem "Walton"
        .AddItem "Hamble"
        .AddItem "Kingsbury"
        .AddItem "Isle of Grain"

    End With

End Sub


'=========================================================================================
'=========================================================================================


Private Sub ComboBox1_Change()

    Dim index As Integer

    index = ComboBox1.ListIndex

    With Me.ComboBox2

        .Clear

        Select Case index

               Case 0

                    .AddItem "Northampton Terminal"
         
               Case 1

                    .AddItem "Walton Terminal"
                    .AddItem "Walton Weir"

               Case 2

                    .AddItem "Hamble Terminal"
                    .AddItem "Hamble Jetty"

               Case 3

                    .AddItem "Kingsbury Terminal"

               Case 4

                   .AddItem "IOG Yard Area"
                   .AddItem "IOG Non Jet A-1 Storage"
                   .AddItem "IOG Jetty"

        End Select

    End With

End Sub


'=========================================================================================
'=========================================================================================


Private Sub CommandButton1_Click()

    Const sPASSWORD     As String = "password"

    Dim sSheetToShow    As String
    Dim wks             As Worksheet

    If Me.ComboBox1.Value <> vbNullString And _
       Me.ComboBox2.Value <> vbNullString Then

          MsgBox "OK to proceed."

          sSheetToShow = Me.ComboBox2.Text

          With ThisWorkbook

              .Unprotect Password:=sPASSWORD

                  For Each wks In ThisWorkbook.Worksheets

                      If wks.Name <> "Index" Then
                          wks.Visible = xlSheetHidden
                      End If

                  Next wks

                  .Worksheets(sSheetToShow).Visible = xlSheetVisible

                  .Worksheets(sSheetToShow).Activate
        
              .Protect Password:=sPASSWORD

          End With

          Unload Me

    Else: MsgBox "Both comboboxes must have a selection."

    End If

End Sub

Selecting a new sheet via the ComboBoxes automatically hides whichever "target" worksheet was selected previously.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Remove the code ActWsh.Visible=xlsheetvisble and replace it with Worksheets(ActWsh).Visible=xlSheetVisible Fixed the code