list - Excel VBA 列表验证 - 特殊的运行时错误

Question

我只是想向单元格添加列表验证，我收到运行时错误 1004 应用程序定义或对象定义错误。我有这个确切的代码来验证我工作簿中其他地方的单元格，它从来没有出现过问题。

With Range("Q3").Validation
        .Delete
        .Add _
        Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Formula1:="=Constants!Q6:Q30"
End With

我也尝试过对 Formula1 使用字符串“Alice,Bob”，但我得到了同样的错误，所以这不是问题。事实上，我只在尝试在此特定工作表上设置任何类型的验证(例如与 xlValidateWholeNumber 相同的错误)时看到此错误。

一些可能相关或不相关的信息:

当我尝试时，工作表未 protected 并且屏幕更新已关闭
设置此验证

以上包含在 CommandButton_Click
所述工作表上的事件

工作簿中的所有命令按钮都有
TakeFocusOnClick = 假

这是 Microsoft Office 2013

Answer 1

我意识到这是一个老问题，但它仍然很出色。

根据我的经验，您不能将数据验证设置为存储在另一张工作表中的范围。
有一个异常(exception):如果您“命名”该范围。

因此，我的解决方法是选择我需要用作引用的范围，给它一个定义的名称(至少在 Excel 2010 中:公式选项卡 > 名称管理器 > 定义名称 > 遵循提示 [或只需在编辑栏旁边的框，您在下图中看到 B2])，然后在我的代码中使用该命名范围。



在您的情况下，假设您的命名范围是“NamedRange01”，您的代码将是:
With Range("Q3").Validation
.Delete
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="=NamedRange01"
End With