使用 VBA 进行单元格验证

Question

>我正在尝试为单元格分配验证列表。验证列表根据某个单元格的值而变化，例如，如果单元格"C6"的值为 28，则验证列表应为范围 Sheet4.Range（"b4：b20"）。如您所见，验证列表来自另一个 sheet.in 执行此操作，我编写了以下代码

 ValrStart = Sheet4.Cells(rowno, 4).Address  ‘rowno is the row in which the validation list starts and its value comes from another part of the code 
ValrEnd = Sheet4.Cells(rownoEnd, 4).Address rownoEnd is the row in which the validation list ends and its value comes from another part of the code 
Rng = "Sheet4.Range(""" & ValrStart & Chr(58) & ValrEnd & """" & ")"
With Cells(20, 3).Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
          Operator:=xlBetween, Formula1:=Rng
          .ErrorMessage = "Invalid value. Select one from the dropdown list."
          check = Cells(20, 3).Validation.Value
          If check = False Then
          Cells(20, 3).ClearContents
          Exit Sub
          End If
        End With

现在发生的事情是，在我看来，单元格中的是字符串 Rng 的值，而不是它所代表的范围。有人可以帮忙吗？谢谢

Answer 1

问题出在 Formula1 参数中，该参数的开头必须有一个"="

我会让 excel 为您完成艰苦的工作，如下所示：

Dim rng As Range '<~~ set rng as of a Range type
With Sheet4
    Set rng = .Range(.Cells(rowno, 4), .Cells(rownoEnd, 4)) '<~~ set rng to the wanted range
End With
With Cells(20, 13).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:="=" & rng.Address(, , , True) '<~~ have Address function take out the correct formula, just add a "=" at the beginning
    .ErrorMessage = "Invalid value. Select one from the dropdown list."
    check = Cells(20, 3).Validation.Value
    If check = False Then
       Cells(20, 3).ClearContents
       Exit Sub
    End If
End With