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??
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).
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.
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.
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With