Option
Explicit
Private
Sub
UserForm_Initialize()
ComboBox1.List = GetVisibleWorksheetNames()
End
Sub
Private
Function
GetVisibleWorksheetNames()
As
Variant
Dim
wksCount
As
Long
wksCount = ThisWorkbook.Worksheets.Count
If
wksCount <= 0
Then
GetVisibleWorksheetNames = Split(Empty)
Exit
Function
End
If
ReDim
wksNames(0
To
wksCount - 1)
Dim
wks
As
Excel.Worksheet
Dim
n
As
Long
For
Each
wks
In
ThisWorkbook.Worksheets
If
wks.Visible = xlSheetVisible
Then
wksNames(n) = wks.Name
n = n + 1
End
If
Next
If
n < wksCount
Then
ReDim
Preserve
wksNames(0
To
n - 1)
End
If
GetVisibleWorksheetNames = wksNames
End
Function