How to add data validation to a cell using VBA

Question

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"

Answer 1

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 list
• AlertStyle:=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.
• in your original code, 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)

Answer 2

If you get confused with setting the options of data validation, I will suggest "recording" a macro and diving into the codes there. For example, I just complete a sub to configure data validation for cells in a batch. Find the requirements and codes below.

Sub setCellDataValidation(idxRow As LongLong)
' column B
With Cells(idxRow, 2).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="E,PE,DE"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Input Format"
    .InputMessage = "E/PE/DE"
    .ErrorMessage = "Only E, PE, and DE allowed."
    .IMEMode = xlIMEModeNoControl
    .ShowInput = True
    .ShowError = True
End With
' column D
With Cells(idxRow, 4).Validation
    .Delete
    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:="0", Formula2:="100"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Input Format"
    .InputMessage = "Integer in 0 - 100"
    .ErrorMessage = "Only Integer in 0 - 100 allowed."
    .IMEMode = xlIMEModeNoControl
    .ShowInput = True
    .ShowError = True
End With
' column E
With Cells(idxRow, 5).Validation
    .Delete
    .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="1/1/2022", Formula2:="12/31/2024"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Input Format"
    .InputMessage = "yyyy-mm-dd in 2022/1/1 to 2024/12/31"
    .ErrorMessage = "Only yyyy-mm-dd in 2022/1/1 to 2024/12/31 allowed."
    .IMEMode = xlIMEModeNoControl
    .ShowInput = True
    .ShowError = True
End With
' column F
With Cells(idxRow, 6).Validation
    .Delete
    .Add Type:=xlValidateTime, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="00:00:00", Formula2:="23:59:59"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Time Format"
    .InputMessage = "hh:mm in 00:00:00 to 23:59:59"
    .ErrorMessage = "Only hh:mm in 00:00:00 to 23:59:59 allowed."
    .IMEMode = xlIMEModeNoControl
    .ShowInput = True
    .ShowError = True
End With
' column W
With Cells(idxRow, 23).Validation
    .Delete
    .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
    :=xlBetween, Formula1:="0.01", Formula2:="2000.99"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Input Format"
    .InputMessage = "3 decimal"
    .ErrorMessage = "Only 3 decimal allowed."
    .IMEMode = xlIMEModeNoControl
    .ShowInput = True
    .ShowError = True
End With
End Sub