I want to add "data validation" in a cell(which is variable) using VBA and the range which is to come in the data validation list is also variable. Till now I have been using this
Here "range1" is the range which is to come in the data validation list and "rng" is the cell where I want the data validation
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
I am getting "application defined and object defined error"
Also can someone explain me the meaning of different arguments in
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="range1"
Use this one:
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
Note that when you're using Dim range1, rng As range
, only rng
has type of Range
, but range1
is Variant
. That's why I'm using Dim range1 As Range, rng As Range
.
About meaning of parameters you can read is MSDN, but in short:
Type:=xlValidateList
means validation type, in that case you should select value from listAlertStyle:=xlValidAlertStop
specifies the icon used in message boxes displayed during validation. If user enters any value out of list, he/she would get error message.Operator:= xlBetween
is odd. It can be used only if two formulas are provided for validation.Formula1:="='" & ws.Name & "'!" & range1.Address
for list data validation provides address of list with values (in format =Sheet!A1:A5
)Source: Stackoverflow.com