How do i filter a range in another sheet with VBA without activating the sheet

Question

Good day,

I am having problems with Set Ranges and it has been quite frustrating when using set ranges from non-active sheets.

The problem is:

I have a sheet called "Dashboard". In this sheet i have a Listbox that when selected will filter values (based on listbox.column value) on a Table in another sheet called "Budget". However, i get Error 1004 (Autofilter method of Range class failed), after closing the error it filters the range. So it seems it works somehow, however it gives me error.

The code below is the one i'm using to filter the range. It is inserted in the "Dashboard" Sheet object.

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

The macro will filter a range that is used for a chart, therefore will filter the values of my chart. Also i don't want to use pivot tables as it is very slow.

Further exploring the question. How can i use Ranges from one Worksheet that are Set in another Worksheet without having to Activate the Sheet of that range? (most of the time i have to do Sheet.Activate before using the Set range for that sheet).

Would you guys know the workaround and why there is this problem with Set Ranges?

I know there are similar questions about ranges, but none with the same specifications.

Additional Information (Edit):

1- Error is on line:

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

2- Listbox index >= 0 to ensure that listbox is not empty and there's an item selected. When a listbox is empty the listindex = -1.

3- lrow(Budget, "A") calls the following function to get the last row in the specified sheet:

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

4- With msgbox rng.address just before the error line, i receive $B$1:$E$5 as the address.

5- I did a temporary workaround using

On Error Resume Next

6- Value for Me.DashboardBudgetlst.Column(1, i) is a keyword to be filtered and depends on the selection. The listbox is fed with the same range that i am filtering. So i am selecting the column "1" from the list which is under the header "Item". When i select something from the listbox i want it to filter by that Budget Item, sometimes can be "Accommodation" or anything else i have there.

7- Debug.Print on :

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

Selected on Travel Expenses in listbox Returns on Immediate Window:

True 1        Travel Expenses

8- Some Screenshots:

Listbox in Dashboard Sheet (Excel View)

Range in Budget Sheet (Excel View)

Objects being used (VBA view)

It works as after i closed the error the filter would apply. However i would like to know if there's another workaround and i'm not sure about using "On Error Resume Next" (Is it bad for your code?)

On which line do you get the error? And how do you "close the error"? Also, what would `llstIndex` be if not `>=0`? Is there a `-1`? — ashleedawg, Apr 09 '18 at 01:38
There were other questions. ...and now two more: what's `lrow`? And what's the value of `rng.Address` just before the error? (Find out by adding a line right before the error: `MsgBox rng.Address`) — ashleedawg, Apr 09 '18 at 01:48
What is the value of this `Me.DashboardBudgetlst.Column(1, i)`? It looks like you are setting the proper ranges to the other sheet, if the sheet **Code Name** is indeed "Budget" — paul bica, Apr 09 '18 at 08:22
@paulbica i edited the question and answered you in point 6. And the name of the Sheet Object (in the VBA) is Budget indeed. It is quite odd this kind of error seems to happen often to me when i am using a set range from another sheet without activating the second sheet in the range. The filter procedure is in the sheet Dashboard code and filters a range in the Budget sheet. Any idea? — Raphael Yaghdjian, Apr 09 '18 at 09:14
The most common problem with working with ranges from other sheets without selecting or activating them is that the ranges are not explicitly qualified: Range("A1") could be on any of the sheets but if it's not qualified the Activesheet will be used by Excel by default (the sheet visible on the screen. You do qualify your range properly as in `Budget.Range("B1...` which is great! **On Error Resume Next** is a bad specially for beginners because you don't understand or know the errors you are hiding - so your motivation is great as well! — paul bica, Apr 09 '18 at 10:09
To fix the current issue you have to study each element in that statement: `rng.AutoFilter 1, Me.DashboardBudgetlst.Column(1, i)`. You confirmed that `rng` is ok: `$B$1:$E$5`, now we know that `1` is the column to be filtered on sheet `Budget`. The only unclear parameter is `Me.DashboardBudgetlst.Column(1, i)` - do MsgBox on it just prior to the filter, or `Debug.Print`, or add it to the Watch window to see the actual value used by the filter, and try to manually filter that column using the same criteria — paul bica, Apr 09 '18 at 10:15
More questions: is the sheet `Budget` protected? Also, is the range `$B$1:$E$5` in a table (ListObject)?, is range `$B$1:$E$5` empty? try enclosing it in quotes: `"""" & Me.DashboardBudgetlst.Column(1, i) & """"` — paul bica, Apr 09 '18 at 10:22
Side note which shouldn't affect the problem: `lrow = SH.Cells(Rows.Count, col).End(xlUp).Row` Is worth using `SH.Rows.Count` in case you've got a mixture of XL2003 & XL2007+ books open. — Darren Bartrup-Cook, Apr 09 '18 at 11:24
You did a lot of research - great work ! One possible issue is with the criteria: it might be trying to apply a filter using this exact string **"`True 1 Travel Expenses`"**. You may want to extract this in a string variable and split it (by spaces), then extract the criteria to be just the last part of the string ("Travel Expenses") — paul bica, Apr 09 '18 at 11:29
@paulbica Added some pics. All sheets unprotected. The range is just a normal range and with values, not a table. I tried enclosing it in quotes and same happened. Debug.print of the filter line gives me: True 1 Travel Expenses. Could it be that the listbox as an ActiveX object and running the code from Dasboard(listbox_change) bugs it? Will try your comment about splitting it thanks. Also Thanks for the tip Darren. — Raphael Yaghdjian, Apr 09 '18 at 11:33
Your solution has worked perfectly @paulbica. It was really the _Change event as you said, i wonder if it happens to any change events or just ActiveX listbox in a sheet. Your commitment was really impressive thank you very much. — Raphael Yaghdjian, Apr 09 '18 at 12:51
I'm glad it helped a bit. ListBoxes can be tricky specially when multi select is on. I haven't used them in a long time, and still need to play some more to figure them out again. Anyway, the `_Change` event is different for multiple items (an ActiveX with single selection is simpler) — paul bica, Apr 09 '18 at 12:59

paul bica · Accepted Answer · 2018-04-09T12:33:49.710

I was able to duplicate the error

Depending on the number of items select in the ListBox, the issue seems to be that there are multiple _Change events being triggered

I was able to stop the error by using an event flag

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

How do i filter a range in another sheet with VBA without activating the sheet

Additional Information (Edit):

1 Answers1