使用这个：

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是奇数。2只有在提供两个公式进行验证时才能使用它。
• Formula1:="='" & ws.Name & "'!" & range1.Address用于列表数据验证，提供列表地址和值（格式为=Sheet!A1:A5）

如果您对设置数据验证选项感到困惑，我建议您“记录”一个宏并深入研究那里的代码。例如，我刚刚完成了一个子程序来配置批处理中单元格的数据验证。请在下面找到要求和代码。

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