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 ..
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 ..