Get worksheet names from workbook, EXCLUDING hidden worksheets

[kazbarina]

Hi,

I've managed to create a form that will list all worksheets in a workbook. However, I have 15 or so hidden worksheets that I do not want populated in this list, as the user cannot select them.

Does anyone know the code I need to add to ensure I only get unhidden sheets. My current code is below. Thanks
===============================================================

Private Sub UserForm_Initialize()
'Gets the names of each worksheet in the workbook from the newly opened file

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
ListBox1.AddItem (WS.Name)
Next WS
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer, sht As String
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
sht = ListBox1.List(i)
End If
Next i
Sheets(sht).Activate
Unload UserForm1
End Sub

 

[VoG]

Try

Private Sub UserForm_Initialize()
'Gets the names of each worksheet in the workbook from the newly opened file

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
    If WS.Visible = xlSheetVisible Then ListBox1.AddItem (WS.Name)
Next WS
End Sub

 

[kazbarina]

Hi,

Many thanks - just what I wanted. It works a treat.