excel - 在 vba 中添加验证导致错误 1004

Question

我在名称管理器中定义了一些名称，例如validation_list1，它指的是=OFFSET(Sheet3!$C$4,,,COUNTIF(Sheet3!$C$4:$C$399,"?*"))

我想使用 vba 添加验证列表。下面的代码导致错误 1004：

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=validation_list1"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = False
    ' Code with error
End With

但是当我从分配给 Formula1 的文本中删除等号 ( = ) 时，一切都会好起来的

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="validation_list1"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = False
    'code without error!
End With

上述两个代码相似，但第一个具有Formula1:="=validation_list1"和第二个具有Formula1:="validation_list1"。为什么第一个会导致错误 1004？我必须添加Formula1:="=validation_list1"，如何？

我对定义 name manager 没有任何问题，问题是在引号之间添加等号（分配给 Formula1 的文本）。文本是什么并不重要，我通过Formula1:="=test"对其进行了测试并且也导致了错误然后删除了 = 符号并写了Formula1:="test"并且一切都很好。

编辑：谢谢克里斯尼尔森。这段代码是Record Macro的输出，如果我手动选择一个单元格并从Data选项卡中选择在Data Validation输入文本=validation_list1Source，一切都会好的，但使用 vba 会导致错误

已解决非常感谢@chrisneilsen ，您的建议帮助我找到了原因。单独的每件事似乎都很好。公式、范围和命名范围都是正确的。此验证发生在程序对工作表和公式进行一些计算和更改之后，因此与验证间接相关的一个单元格更改为 #REF 。我使用定义名称作为该单元格的公式和 BINGO

标签： excelvba

Answer 1

如您所知，您必须=在 Formula1 作业中包含 。如果不这样做，那么您分配的是文字字符串，而不是命名范围。

分配 Formula1 值时，如果出现以下情况，将出现此错误：

• 命名范围不存在（如您尝试时发生的那样Formula1:="=test"），或
• 命名范围是工作表范围的，您尝试在不同工作表上的验证中使用它
• 命名范围存在，但其“RefersTo”无效

我试过你的设置，命名范围和代码。它对我有用，使用 Excel 365

Sub test()
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=validation_list1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
End Sub

名称定义

验证列表数据

B4:B5选择运行代码后

证明命名范围有效

检查事项

• 检查validation_list1工作簿范围
• 中的数据Sheet3!C4:C399是有效的（没有错误，没有间隙）
• 命名范围名称或公式中没有拼写错误
• 输入=validation_list1一个单元格。你看到了什么？