Simon Lloyd
05-05-2007, 03:43 AM
Hi all, i am trying to build a list of sheet names when a worksheet is activated and then create List Validation in a cell using this list as the row source, here's what i have but i dont know what to declare ShList as and the code stops at this line with error 91 or 424 depending on how i define ShList.
Any ideas?
Dim ShList as
For Each Worksheet In Worksheets
ShList.AddItem Worksheet.Name
Next
Sheets("Switchboard").Range("A1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ShList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Regards,
SImon
Any ideas?
Dim ShList as
For Each Worksheet In Worksheets
ShList.AddItem Worksheet.Name
Next
Sheets("Switchboard").Range("A1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ShList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Regards,
SImon