How to create a data validation list using VBA and a comma delimited list

I happened to have something laying around on the bench that I could modify

The macro CreateList does the dirty work, but you need to pass it the parameters. SheetName is the name of the sheet on which you want to place the validation. CellName is the cell address on which you want to place the validation, LookupString is the lookup string that is the source of the validation. You can either "assemble" it in code, or in the example, I put it in a named range and read that.

Sub CreateList(SheetName As String, CellName As String, LookupString As String)
' SheetName = Sheet where you want the drop-down list
' CellName = Address of the cell where you want the drop-down

Dim Sh As Worksheet             ' General pointer to sheets in the workbook
Dim shD As Worksheet            ' Pointer to the sheet on which you want the drop-down
Dim ListString As String        ' List of the sheet names

' Initalize variables
Set shD = Sheets(SheetName)
ListString = ""



' "Remove" the trailing comma from the string
ListString = LookupString

' Make the validation
With shD.Range(CellName).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=ListString
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
End Sub

Sub ApplyLookup()
CreateList "Sheet1", "B2", Range("Lookup_List")
End Sub