Как добавить проверку данных в ячейку с помощью VBA

Я хочу добавить "проверку данных" в ячейке (которая является переменной) с помощью VBA, а диапазон, который должен войти в список проверки данных, также является переменной. До сих пор я использовал этот

Здесь "range1" - это диапазон, который должен войти в список проверки данных, а "rng" - это ячейка, где я хочу, чтобы проверка данных

Dim range1, rng As range
Set range1 = range("a1:a5")
Set rng = range("b1")
With rng
With .Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="range1"
End With
End With

Я получаю "определение приложения и объектная ошибка"

Также может кто-нибудь объяснить мне смысл разных аргументов в

With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="range1"

Ответ 1

Используйте это:

Dim ws As Worksheet
Dim range1 As Range, rng As Range
'change Sheet1 to suit
Set ws = ThisWorkbook.Worksheets("Sheet1")

Set range1 = ws.Range("A1:A5")
Set rng = ws.Range("B1")

With rng.Validation
    .Delete 'delete previous validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="='" & ws.Name & "'!" & range1.Address
End With

Обратите внимание, что, когда вы используете Dim range1, rng As range, только rng имеет тип Range, но range1 - Variant. Вот почему я использую Dim range1 As Range, rng As Range.
О значении параметров, которые вы можете прочитать, MSDN, но вкратце:

Type:=xlValidateList означает тип проверки, в этом случае вы должны выбрать значение из списка
AlertStyle:=xlValidAlertStop указывает значок, используемый в сообщениях, отображаемых во время проверки. Если пользователь вводит любое значение из списка, он/она получит сообщение об ошибке.
в исходном коде Operator:= xlBetween нечетно. Он может использоваться только в том случае, если для проверки предусмотрены две формулы.
Formula1:="='" & ws.Name & "'!" & range1.Address для проверки данных списка предоставляет адрес списка со значениями (в формате =Sheet!A1:A5)

Ответ 2

У меня проблемы с получением вышеуказанного кода для работы. Позвольте мне убедиться, что я понимаю. Мой rngB - это диапазон для применения проверки данных. rngl, где находится список (столбец G). .Добавить тип: линия взрывается независимо от того, что я пытаюсь...

intFRow = wksLookups.Cells(Rows.Count, "G").End(xlUp).row
Set rngB = SalesBudg.Range("I8:I9999")
Set rngL = wksLookups.Range("G2:G" & CStr(intFRow))

With rngB.Validation
    .Delete 'delete previous validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=wksLookups!" & rngL.Address
End With