Create data validation list when some of the values have commas?
I am creating a data validation list using the following method:
sDataValidationList = sDataValidationList & wksCalculation.Cells(r, lActivityNoColumn).value & ","
Then I apply it to a cell using:
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=s
This works well most of the time, but when any of the
wksCalculation.Cells(r, lActivityNoColumn).value contain commas, then those strings are split by the data validation list and each comma separated part of the string is shown as a separate item.
How can I modify my code to be useful also when some of the values that go into the data validation list have commas in them?
In data validation with
Formula1 can either be a comma separated list or a formula string which is a reference to a range with that list. In a comma separated list the comma has special meaning. In a reference to a range it has not.
So supposed your list, which you are concatenating from
wksCalculation.Cells(r, lActivityNoColumn), is in
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Sheet2!A1:A5"
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & wksCalculation.Range("A1:A5").Address(External:=True)
should also work.
Create a drop-down list where the list is separate sentences that , I have a google sheet where I would like each person to select a sentence from a list. However, when adding each sentence to the data validation criteria, google How do I keep my list intact without removing the commas? Some community members might have badges that indicate their identity or How can I modify my code to be useful also when some of the values that go into the data validation list have commas in them? Answer: In data validation with Type:=xlValidateList the Formula1 can either be a comma separated list or a formula string which is a reference to a range with that list. In a comma separated list the comma has special meaning.
You will have to Trick Excel ;)
Here is an example. Replace that comma with a similar looking character whose ASC code is
Dim dList As String dList = Range("B14").Value '~~> Replace comma with a similar looking character dList = Replace(dList, ",", Chr(130)) With Range("D14").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=dList .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With
Data validation list with a comma, My list values are comma separated. When you need the list (for your data validation, for example), simply point it to the Named Range. =CityList. I think that this will allow you to use commas without any problem. To create a drop-down list, start by going to the Data tab on the Ribbon and click the Data Validation button. The Data Validation window will appear. The keyboard shortcut to open the Data Validation window is Alt, A, V, V. You’ll want to select List in the drop-down menu under Allow.
Create a named range
ValidValues that contains your list items; use that named range in the data validation formula
=ValidValues. If the number of valid values can change, I suggest you make the named range refer to a table column, like
SomeTable[SomeColumn] - that way any new value in that column will automatically be part of the named range and thus, automatically added to the list of valid values.
Data Validation of a Comma Delimited List, You're on the right track with using the commas to identify the individual numbers. This works for 50 comma separated values - if you're in Excel 2010 this can be In data validation with Type:=xlValidateList the Formula1 can either be a comma separated list or a formula string which is a reference to a range with that list. In a comma separated list the comma has special meaning. In a reference to a range it has not.
You can use the ASCI character "ALT-0130". This will look like a comma but it won't be used to separate in the validation.
[SOLVED] Show comma in data validation list?, I want the following 3 items in a data validation list. Note they have commas in the items. Apple, Kiwi and some manually calculated results. And if the user always chooses the value using the dropdown, then this won't Open the Data Validation dialog box (Data tab > Data Validation). On the Settings tab, select List in the Allow In the Source box, type the items of your Excel validation list, separated by commas. For example, to limit the user input to three choices, type Yes, No, N/A.
Comma in Excel Data Validation Source, Comma in Excel Data Validation Source For example, I want to have the following three options in the list. Since comma is the default separator for list values, the list will become Make now a choice from C2. I have some code which works very well when a search is made through a user form and To create a drop down list, simply use the ‘Data’ -> ‘Validation’ menu option in Excel 2003 or click on the ‘Data’ tab on the ribbon and then ‘Data Validation’ in Excel 2007 and later. Once you have the data validation option box open, simply type in the values as shown in the image above. Once done, click Ok.
Data Validation with a string in a cell, I have used Textjoin to create a string with comma delimiter in cell C1, the data validation list, you are entering individual values (which may be texts) separated by commas. Is there any workaround to get this idea done? To create a drop down list in Excel 2010, you can name a list of items, based on a named Excel table. Then, use that list as the source for the Data Validation drop down list. If you don't want to create a named table, you can follow the instructions in the named range section below.
Excel Drop Down Lists - Data Validation, Create a drop down list of items in a cell, with Excel's Data Validation feature. Video tutorials and step by step instructions are below such as a date range or whole numbers only; create custom rules for what can be entered Your browser does not currently recognize any of the video formats available. According to MSDN Validation.Add, the comma always separate the entries. One workaround is to use Dynamic Named Range. Use a spare column in a hidden sheet or somewhere, lets use Column A for demo. In A1, put in "DV_ListTable", Define the cell with name DV_ListTable. Highlight Column A, Define it as DV_ListColumn.