Solved: Build list of sheet names and create cell validation?

[Simon Lloyd]

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

[Simon Lloyd]

Well no matter what i tried i couldnt get ShList to represent an object even if i removed ShList and put in Sheets("Switchboard").Range("A1") in its place it wouldn't work (i know that .Range("A1") isn't the object but it was my target for the list). Anyway i solved it like this:

Dim ShList As Object
For Each ShList In ThisWorkbook.Sheets
Sheets("Switchboard").ComboBox1.AddItem ShList.Name
Next ShList
yep!, added a combobox, not what i wanted to do but a lot easier and a lot less code!

Regards,
Simon

[Bob Phillips]

Dim ShList As String
Dim i As Long

For i = 1 To Worksheets.Count
ShList = ShList & Worksheets(i).Name & ","
Next
ShList = Left(ShList, Len(ShList) - 1)
With Sheets("Switchboard").Range("A1").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

[Bob Phillips]

At home, I just use the other box, but on the road it is a real pain.