Create data validation list when some of the values have commas?
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:425 плюса
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
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.
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Вопросы из категории :
- excel Обновить результаты функции Excel VBA
- excel Чтение файлов Excel из C #
- excel Как найти последнюю строку, содержащую данные на листе Excel с макросом?
- excel Лучший способ выполнения контроля версий для MS Excel
- excel Как создать файл Excel (.XLS и .XLSX) в C # без установки MS Office?
- vba Функция сортировки массива VBA?
- vba Получение Excel для обновления данных на листе из VBA
- vba Как я могу отправить HTTP-запрос POST на сервер из Excel, используя VBA?
- validation Валидация клиентской стороны ASP.Net
- validation Проверить перечисление значений
- validation Каков наилучший способ проверки XML-файла по сравнению с XSD-файлом?
- validation Каков наилучший способ проверки полей с использованием ASP.NET MVC?
- validation Проверка десятичных чисел в JavaScript - IsNumeric ()
- excel-vba Относительные вместо абсолютных путей в Excel VBA
- excel-vba Как я могу кодировать строку в Excel в VBA?