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