Validating data to include a comma

Question

I use 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 change the macro to include a comma in the list. I do not see how to do this because the comma is a list separator.

Am I stuck to use sheet cells to create a list?

Answer 1

As far as I can check, you cannot escape the list.

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