Create a data validation list when some values ​​have commas?

Question

I create a data validation list using the following method:

sDataValidationList = sDataValidationList & wksCalculation.Cells(r, lActivityNoColumn).value & "," 

Then I apply it to the cell using:

 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=s 

This works many times, but when any of wksCalculation.Cells(r, lActivityNoColumn).value contains commas, these lines are broken into a data validation list, and each part separated by commas is shown as a separate element.

How can I change my code to be useful also when some of the values ​​that are on the data validation list contain commas in them?

Answer 1

When validating data with Type:=xlValidateList Formula1 can be either a comma separated list or a formula string that is a reference to a range with this list. In a comma-separated list, a comma has special meaning. In reference to the range it does not have.

So, suppose your list, which you associate with wksCalculation.Cells(r, lActivityNoColumn) , is in Sheet2!A1:A5 , then

 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Sheet2!A1:A5" 

will work.

 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & wksCalculation.Range("A1:A5").Address(External:=True) 

should also work.

Answer 2

You will need Trick Excel;)

Here is an example. Replace this comma with the same look, ASC code 0130

 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 

Answer 3

Create a named range ValidValues containing list items; use this named range in the data validation formula =ValidValues . If the number of allowed values ​​can change, I suggest that you make the named range a reference to a column of the table, for example SomeTable[SomeColumn] - this way any new value in this column will automatically be part of the named range and thus will automatically be added to the list of valid values.

Answer 4

You can use the ASCI character "ALT-0130". It will look like a comma, but will not be used for separation during validation.