Search code examples
excelvbadropdown

Excel drop down shows all options in just one line as one single comma separated option

I've created a drop down list through VBA. sometimes it shows all options as on single option. sometimes switching between sheets fix the problem, sometimes not! Here is the code:

Dim arr1(8) As String
    arr1(0) = "EURUSD"
    arr1(1) = "GBPUSD"
    arr1(2) = "USDCHF"
    arr1(3) = "USDJPY"
    arr1(4) = "USDCAD"
    arr1(5) = "AUDUSD"
    arr1(6) = "NZDUSD"
    arr1(7) = "XAUUSD"

With Range("C" & 1).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(arr1, ",")
End With

I appreciate any help with this issue.

Drop down list mixed up all options

Solution

  • Seems like the List Separator setting (Control Panel | Regional Options) is something other than a Comma (,). For example, if your locale setting is Germany, then I guess your list separator would be a Semicolon (;). In VBE, type ?Application.International(xlListSeparator) to quickly check what is your List Separator.

    If you are doing it manually from the Data Tab then use the correct List Separator. For example YES;NO

    If you are doing it via VBA then use Application.International(xlListSeparator)

    In your code replace Formula1:=Join(arr1, ",") with Formula1:=Join(arr1, Application.International(xlListSeparator))