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

excel vba validation excel-vba

4182 просмотра

4 ответа

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?

Автор: user1283776 Источник Размещён: 12.11.2019 09:40

Ответы (4)

4 плюса

Решение

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.

Автор: Axel Richter Размещён: 09.06.2015 06:42

5 плюса

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

Автор: Siddharth Rout Размещён: 09.06.2015 06:26

2 плюса

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.

Автор: Mathieu Guindon Размещён: 09.06.2015 06:27

0 плюса

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.

Автор: Shane Размещён: 08.10.2019 11:19
32x32