Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Data Validation to Include Comma Character

Tags:

excel

vba

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??

like image 523
Gary's Student Avatar asked Sep 10 '13 15:09

Gary's Student


People also ask

How do I add a comma to a drop down list in Excel?

Enter the list items and choose the options. In the Data Validation window, on the Settings tab, do the following: In the Allow box, select List. In the Source box, type the items you want to appear in your drop-down menu separated by a comma (with or without spaces).

How do I validate a character in Excel?

1. Select the column you want to limit the entry, and click Data > Data Validation > Data Validation. 3. Click OK, and then the selected column only allowed entry numeric characters.

Can you have conditional data validation in Excel?

This type of conditional data entry in excel can be done using the data validation feature in Excel. It can enable data entry in the specified cells only when the specified conditions are met, else it shows an error.


1 Answers

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.

like image 76
LS_ᴅᴇᴠ Avatar answered Oct 17 '22 10:10

LS_ᴅᴇᴠ