Create data validation list when some of the values have commas?

Question

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?

Answer 1

You will have to Trick Excel ;)

Here is an example. Replace that comma with a similar looking character whose ASC code is 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 2

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.

So supposed your list, which you are concatenating from 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.