PDA

View Full Version : do not display hide worksheets in data validation list



white_flag
01-28-2011, 06:57 AM
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
01-28-2011, 08:19 AM
There is probably a shorter way but this seems to work ok.
'-----------------------------------------------------------------------------
'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