Excel：在 VBA 子文件中设置验证

安东尼瓜尔维亚

我正在尝试使用 sub 来配置来自表不同范围的列表的验证，以简化主例程的外观，我可以找到验证器的正确形式以输入到 Formula1 参数。你能帮助我吗？

 'in the main routine
  add_validation  Range.Columns("A"), range("types")
  add_validation  Range.Columns("C"), range("units") 
  ....

  'the sub 
  Sub add_validation(to_validate As Range, validator As Range)
      With to_validate
        With .Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=validator
        End With
    End With
    End Sub

苦艾酒

您的Range争论不太正确，验证公式需要一个字符串。做这个：

 'in the main routine
  add_validation  Columns("A:A"), "types" ' Columns is the range object, you don't need Range.Column
  add_validation  Columns("C:C"), "units" ' Your named range is accessed via a string for cell validation
  ....

  'the sub 
  Sub add_validation(to_validate As Range, validator As String)
      With to_validate
        With .Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & validator
        End With
    End With
    End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。