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
Bookmarks