How to use drop down list to only show hidden worksheets

[Jazz689]

I have the below code that currently shows all worksheeets in the workbook. I would like to modify it to only show the hidden worksheets.

Private Sub ComboBox1_Change()
'Updateby Extendoffice
If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub
Private Sub ComboBox1_DropButt*******()
Dim xSheet As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
If ComboBox1.ListCount <> ThisWorkbook.Sheets.Count Then
ComboBox1.Clear
For Each xSheet In ThisWorkbook.Sheets
If xSheet.Visible Then
ComboBox1.AddItem xSheet.Name
End If
Next xSheet
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub ComboBox1_GotFocus()
If ComboBox1.ListCount <> 0 Then ComboBox1.DropDown
End Sub

 

[Zanmato]

Hi, try putting the below in your code and seeing if it works. It worked for me but I didn't try it alongside all the other code you have there, just on its own.

For Each xSheet In ThisWorkbook.Sheets
If xSheet.Visible = xlSheetHidden Or _
xSheet.Visible = xlSheetVeryHidden Then
ComboBox1.AddItem xSheet.Name
End If
Next xSheet

 

[Jazz689]

I tried replacing what you have there in my code but it does not work. I also tried it on it's own and it still does not work.

 

[Zanmato]

Hmm... when you tried it on its own did you still include the "Dim xSheet As Worksheet" line? I left it out of my post assuming you'd know to leave it in.

EDIT: When you say it's not working - are you getting any error messages or is it just not adding anything into the box?

This is the full thing that is definitely working* for me:

Private Sub ComboBox1_DropButt*******()
Dim xSheet As Worksheet

For Each xSheet In ThisWorkbook.Sheets
If xSheet.Visible = xlSheetHidden Or _
xSheet.Visible = xlSheetVeryHidden Then
ComboBox1.AddItem xSheet.Name
End If
Next xSheet

End Sub

*When I say working, it only works properly the first time you click the drop down button, if you click it again it adds the sheets multiple times. I am guessing your ComboBox1.Clear line is in there to avoid this problem?

Is this combobox of yours on a Userform or just on your sheet? If it's on a Userform you can remove the code from the DropButton sub and put it in this sub instead:

Private Sub UserForm_Initialize()
Dim xSheet As Worksheet

For Each xSheet In ThisWorkbook.Sheets
If xSheet.Visible = xlSheetHidden Or _
xSheet.Visible = xlSheetVeryHidden Then
ComboBox1.AddItem xSheet.Name
End If
Next xSheet
End Sub

That way it will only add the items when you open the form up.

If the ComboBox is NOT on a Userform I may not be able to help, I am not very good with this stuff myself (I'm actually trying to improve by going through threads on here and seeing if I can solve people's problems) and I've never worked with objects inserted directly on to spreadsheets before.

 

[Jazz689]

Allow me to clarify: the way the code was written was to also execute the selection of whichever worksheet is selected in the drop down menu. This is the functionality in your code that is not working. The line of code that I need updated is:
If xSheet.Visible Then
Combobox1.AddItem xSheetName

The way my code is currently works when the sheets are visible. I want to hide the sheets and have it only show the hidden sheets. To do this, I have changed the above code to:
If xSheet.Visible = x1SheetHidden Or _
xSheet.Visible = x1SheetVeryHidden Then
Combobox1.AddItem xSheet.Name

While this does in fact only show the names of each of the hidden sheets, it gives me this error when I try to select one of the hidden sheets:
Run-time error '1004':
Select method of Worksheet class failed

 

[Steve_]

While this does in fact only show the names of each of the hidden sheets, it gives me this error when I try to select one of the hidden sheets:
Run-time error '1004':
Select method of Worksheet class failed

The below code meets your specifications.

Can you talk more about the above quote though please? Are you running code against the hidden sheets when you select them in the combobox?

Sub go()
    UserForm1.ComboBox1.Clear
    For Each ws In ThisWorkbook.Sheets
        If Not ws.Visible Then
            UserForm1.ComboBox1.AddItem ws.Name
        End If
    Next ws
    UserForm1.Show
End Sub

 

[Jazz689]

The worksheets are currently hidden and what I intend to happen is to unhide the sheet when the option is selected from the combobox. There is no user form.