vba - 如何使用 VBA 过滤另一个工作表中的范围而不激活工作表

Question

美好的一天，

我在设置范围方面遇到问题，并且在使用非事件工作表中的设置范围时非常令人沮丧。

问题是:

我有一个名为“仪表板”的工作表。在此工作表中，我有一个列表框，选择该列表框时将过滤另一个名为“预算”的工作表中的表上的值(基于 listbox.column 值)。但是，我收到错误 1004(Range 类的自动筛选方法失败)，在关闭错误后，它会筛选范围。所以看起来它以某种方式工作，但是它给了我错误。

下面的代码是我用来过滤范围的代码。它被插入到“仪表板”工作表对象中。

Private Sub DashboardBudgetlst_Change()

Dim rng As Range
Dim i As Integer

  i = Me.DashboardBudgetlst.ListIndex

    If i >= 0 Then
     If Me.DashboardBudgetlst.Selected(i) And Me.DashboardBudgetlst.Column(0, i) <> "" Then
          Set rng = Budget.Range("B1:E" & lrow(Budget, "A"))
          rng.AutoFilter 1, Me.DashboardBudgetlst.Column(1, i)
          Set rng = Nothing
     End If
   End If

End Sub

该宏将过滤用于图表的范围，因此将过滤我的图表的值。另外我不想使用数据透视表，因为它非常慢。

进一步探讨这个问题。如何使用在另一个工作表中设置的一个工作表中的范围，而无需激活该范围的工作表？ (大多数时候我必须在使用该工作表的设置范围之前执行 Sheet.Activate )。

你们知道解决方法以及为什么设置范围会出现这个问题吗？

我知道也有关于范围的类似问题，但没有一个具有相同的规范。

其他信息(编辑):

1-在线错误:

 rng.AutoFilter 1, Me.DashboardBudgetlst.Column(1, i)

2- 列表框索引 >= 0 以确保列表框不为空并且已选择一个项目。当列表框为空时，列表索引 = -1。

3- lrow(Budget, "A") 调用以下函数来获取指定工作表中的最后一行:

   Function lrow(SH As Worksheet, col As String)
        lrow = SH.Cells(Rows.Count, col).End(xlUp).Row
   End Function

4- 在错误行之前使用 msgbox rng.address，我收到 $B$1:$E$5 作为地址。

5-我使用了一个临时解决方法

On Error Resume Next

6- Me.DashboardBudgetlst.Column(1, i) 的值是要过滤的关键字，取决于选择。列表框的输入范围与我正在过滤的范围相同。因此，我从标题“Item”下的列表中选择列“1”。当我从列表框中选择某些内容时，我希望它按该预算项目进行过滤，有时可以是“住宿”或我在那里的任何其他内容。

7- 调试.打印:

Debug.Print rng.AutoFilter; 1, Me.DashboardBudgetlst.Column(1, i)

在列表框中选择差旅费用返回立即窗口:

True 1        Travel Expenses

8-一些屏幕截图:

仪表板表中的列表框(Excel View )

预算表中的范围(Excel View )

正在使用的对象(VBA View )

它的工作原理就像我关闭错误后过滤器将应用一样。但是我想知道是否还有其他解决方法，并且我不确定是否使用“On Error Resume Next”(这对您的代码有害吗？)

Answer 1

我能够重复该错误

根据列表框中选择的项目数量，问题似乎是触发了多个 _Change 事件

我能够通过使用事件标志来停止错误

<小时/>

Option Explicit

Private Sub DashboardBudgetlst_Change()
    Dim rng As Range, i As Long, lstItm As String, crit As String, startIndex As Long

    If Application.EnableEvents = False Then Exit Sub    'If flag is Off exit Sub

    Application.EnableEvents = False    'Turn flag Off
    With Me.DashboardBudgetlst
        i = .ListIndex
        If i >= 0 Then
            If .Selected(i) And .Column(0, i) <> "" Then

                Set rng = Budget.Range("B1:E5") ' & lrow(Budget, "A"))
                rng.AutoFilter 1, .Value

            End If
        End If
    End With
    Application.EnableEvents = True    'Turn flag back On
End Sub

<小时/>