do not display hide worksheets in data validation list

[white_flag]

Hello

I have this code (I founded on net):

'-----------------------------------------------------------------------------
'Procedure to update the validation list in cell "A1" of the first worksheet with
' a list of all worksheet names. It uses the AllWorkSheets function to create an 
' array of worksheet names.
'-----------------------------------------------------------------------------
Sub UpdateValidationList()
    
    Dim wsArray As Variant
    Dim sWsList As String
    Dim x As Integer
    
    wsArray = AllWorkSheets()
    
    'Separate array of worksheet names into a string separated by commas.
    sWsList = Join(wsArray, ",")
    
    'Add sWsList string to data validation for "A1"
    With Sheets(1).Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=sWsList
    End With
End Sub


'-----------------------------------------------------------------------------
'Function to return an array of all worksheet names
'-----------------------------------------------------------------------------
Public Function AllWorkSheets() As Variant
    Dim wsArray() As Variant
    Dim x As Integer
    
    ReDim wsArray(Sheets.Count - 1)
    
    For x = 0 To Sheets.Count - 1
        wsArray(x) = Sheets(x + 1).Name
    Next x
    
    AllWorkSheets = wsArray
    
End Function

This one create an validation list based on worksheet in specific location. Anyway, my question? Can be this code made, somehow to do not display the hiden worksheets ..

[Kenneth Hobs]

There is probably a shorter way but this seems to work ok.
[VBA]'-----------------------------------------------------------------------------
'Procedure to update the validation list in cell "A1" of the first worksheet with
' a list of all worksheet names. It uses the AllWorkSheets function to create an
' array of worksheet names.
'-----------------------------------------------------------------------------
Sub UpdateValidationList()

Dim wsArray As Variant
Dim sWsList As String
Dim x As Integer

wsArray = AllWorkSheets(True)
'wsArray = AllWorkSheets()

'Separate array of worksheet names into a string separated by commas.
sWsList = Join(wsArray, ",")

'Add sWsList string to data validation for "A1"
With Sheets(1).Range("A1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=sWsList
End With
End Sub


'-----------------------------------------------------------------------------
'Function to return an array of all worksheet names
'-----------------------------------------------------------------------------
Public Function AllWorkSheets(Optional tfOnlyVisible = False) As Variant
Dim wsArray() As Variant
Dim x As Integer, ws As Worksheet

If tfOnlyVisible Then
x = 0
For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then x = x + 1
Next ws
Else: x = Sheets.Count
End If

ReDim wsArray(1 To x)

x = 0
If tfOnlyVisible Then
For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then
x = x + 1
wsArray(x) = ws.Name
End If
Next ws
Else
For Each ws In Worksheets
x = x + 1
wsArray(x) = ws.Name
Next ws
End If

AllWorkSheets = wsArray
End Function

[/VBA]