VBA to populate data validation drop down

[SloFlyer]

I'm looking for a macro that executes when I open my spreadsheet. I need it to create a list of my tab names that I can use as my data validation drop down list. I have found that if I have a range name containing the tab names, I can use it in my data validation list (for example typeing in "=tabnames" in the list range of the data validation selection. I would prefer to have the list residing in my range names as opposed to on a sheet somewhere. Any advice is welcome.

 

[MorganO]

The only way that I can come up with that will do what you want is to build the list of worksheet names within the validation list rather than refering to a range. This keeps you from needing to read it from a worksheet. The following code will build a validation list consisting of the sheet names for the currently selected cell:

Sub test()

FormulaBuilder = ""

For i = 1 To Sheets.Count
FormulaBuilder = FormulaBuilder & Sheets(i).Name & ","
Next
'strip off ending comma
FormulaBuilder = Left(FormulaBuilder, Len(FormulaBuilder) - 1)

With Selection.Validation
        .Delete
       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=FormulaBuilder
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
End With

End Sub

Good Luck.

Owen

 

[SloFlyer]

Owen, this is what I wanted. Works great! Thanks.

 

[sreyes]

I am a newbie so I need exactly how to set up this function in excel.

Thanks!
sreyes