excel - 当我指的是未命名的范围时出现运行时错误“1004”

问题描述

我正在尝试在 Excel 工作表中放置一个下拉菜单，其中包含另一个工作表中的选项，以及另一个工作簿中的选项。我使用 VBA 是因为我想根据另一个下拉菜单自定义这些选项。我设法通过命名源工作表中的范围来做到这一点，但该文件是只读的，因此我无法保存名称以自动执行该过程。所以我尝试只使用引用中的列名（如 $B:$B，我只需要 B 列），但是当我运行宏时，它会因运行时错误 1004 而停止。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("H6")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Call main
    End If
End Sub

Private Sub main()
    reason = Range("H6").Value
    List = ""

    If reason = "Project ID/Task ID" Then
        List = "=PIDTID"
    Else
        List = "=Cost_Center"
    End If

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

如您所见，这是我的宏，在main()更改 H6 时调用，因为那是我的另一个下拉菜单所在的位置。然后，根据该单元格中的值，为 List 分配一个等于定义名称的值，该名称引用另一个工作表中的范围。

例如，如果 PIDTID 名称等于='[20190812_WP_and_CostCenters_Responsible.xls]Projects responsibles'!WBSName，则脚本有效。请注意，WBSName 是源工作表中 B 列的名称。

如果我将 PIDTID 设置为='[20190812_WP_and_CostCenters_Responsible.xls]Projects responsibles'!$B:$B我将在线收到错误

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:=List

Cost_Centername 的作用相同。

我想提一下，这些测试是在打开源文件的情况下完成的。如果我尝试关闭它，我会得到同样的错误。

有没有办法摆脱错误，或者我可以通过其他方式引用其他工作簿工作表中的单元格而不命名它们？我也想工作而不必打开源文件。谢谢！

标签： excelvbaworksheet-functionworksheet

正如 JvdV 在评论中所写，这是因为您不能在列表引用另一个工作簿的情况下使用数据验证，至少不是以简单的方式。

您可以通过使用此公式将命名范围添加到可写工作簿来绕过它（假设Cost_Center在 C 列中）

=IF(Sheet1!H6="PIDTID",OFFSET('[20190812_WP_and_CostCenters_Responsible.xls]Projects responsibles'!$B:$B,0,0),OFFSET('[20190812_WP_and_CostCenters_Responsible.xls]Projects responsibles'!$C:$C,0,0))

然后在您的数据验证/宏中使用这个命名范围。缺点是必须打开源工作簿才能使下拉菜单起作用

excel - 当我指的是未命名的范围时出现运行时错误“1004”

问题描述

解决方案

推荐阅读