CODEWITHSUNDEEP
vbavalidationexcel
user1283776
user1283776

Reputation: 21784

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?

Upvotes: 6

Views: 7596

Answers (4)

Shane
Shane

Reputation: 1

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.

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61915

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.

Upvotes: 5

Siddharth Rout
Siddharth Rout

Reputation: 149315

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

enter image description here

Upvotes: 6

Mathieu Guindon
Mathieu Guindon

Reputation: 71207

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.

Upvotes: 2

Related Questions