Data Validation to Include Comma Character

Question

I am using the following short macro to assign Data Validation as a list of characters:

Sub DVList()
    With ActiveCell.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="a,b,c,d"
        .IgnoreBlank = True
    End With
End Sub

The macro works.

I want to modify the macro to include the comma character in the list. I don't see how to do this because the comma is the list separator.

Am I stuck having to use worksheet cells to build the list??

@pnuts I don't know how to build =CHAR(44) into my Formula1 string — Gary's Student
@pnuts Writing "a,b" is absolutely the same as "a"&Chr(44)&"b"... But I noticed now: you are disregarding VBA. — LS_ᴅᴇᴠ
@pnuts Ok, I thought it because you misused CHAR instead of Chr. Keep in mind that you can use Chr when you want to insert a unsupported character in VBA strings. , doesn't apply, it is valid within strings. Problem is that is a list separator. — LS_ᴅᴇᴠ

LS_ᴅᴇᴠ LS_ᴅᴇᴠ · Accepted Answer · 2013-09-10T16:02:01

A far as I could test, you can't escape , in list.

But you can reference a range. You can build a range (in, eg, a hidden sheet), fill cells with all possibilities and make Formula1 := "=HiddenSheet!A1:A10.

Data Validation to Include Comma Character

1 Answers