Unhiding very hidden sheet Excel VBA

Question

I am trying to create a user form that will unhide a specific worksheet based on the value of the combo box on the user form. There are 3 different worksheets that are " very hidden" in the workbook. The combo box is populated with 3 choices, one for each hidden sheet. I am using select case to make the correct sheet visible (Eventually there will be many more than 3 sheets/options. Sample code follows (located in the user form code window):

Private Sub NextButton_Click()

Select Case ComboBox
    Case ComboBox.ListIndex = 0
        Sheets(1).Visible = True
    Case ComboBox.ListIndex = 1
        Sheets(2).Visible = True
    Case ComboBox.ListIndex = 2
        Sheets(3).Visible = True
End Select
Unload UserForm
End Sub

I click the next button, the userform unloads, but the sheets does not become visible. VBA brings up no errors either. Please let me know if I need to provide any more information.

Nik

mason mason · Accepted Answer · 2014-06-17T20:37:32

Your case statement is incorrect. You should tell it what value to test in the first part, then specify the values later. See Tech on the Net article about Case in VBA.

Private Sub NextButton_Click()

Select Case ComboBox.ListIndex
    Case 0
        Sheets(1).Visible = True
    Case 1
        Sheets(2).Visible = True
    Case 2
        Sheets(3).Visible = True
End Select
Unload UserForm
End Sub

This is how case statements in most programming languages work.

However, since ComboBox.ListIndex is an int, and you're telling it what sheet to show based on that, you could simplify the whole thing and drop the case statement. This presumes the indexes match up, which they do in your example.

Private Sub NextButton_Click()  
Sheets(ComboBox.ListIndex+1).Visible = True
Unload UserForm
End Sub

Unhiding very hidden sheet Excel VBA

1 Answers