VBA anomaly when filtering table from Worksheet_Calculate

[Jerry Sullivan]

When the user filters Table1, my objective is to have Table2 on the same sheet filtered so that both tables have the same ID items visible.

Since VBA doesn't have an event that is triggered when a table is filtered, I'm using the Worksheet_Calculate event which is triggered due to the presence a SUBTOTAL formula that counts visible rows in Table1.

The problem is that VBA doesn't seem to be able to AutoFilter Table2 if Worksheet_Calculate is triggered as a result of filtering Table1.

Here's a simplified example to illustrate the problem...

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F
1	Table1
2	ID	Name	Measure
3	#111	Name1	277		Test	4
4	#222	Name2	155		Formula:	5
5	#333	Name3	296
6	#444	Name4	109
7		Subtotal:	837
8
9	Table 2
10	ID	Name	Measure
11	#111	Name1	637
12	#222	Name2	476
13	#333	Name3	454
14	#444	Name4	127
15		Subtotal:	1694
Sheet1

Cell Formulas
Range	Formula
F4	=F3+1
C7	=SUBTOTAL(9,Table1[Measure])
C15	=SUBTOTAL(9,Table2[Measure])

Note: My finished code will read the visible items' IDs in Table1 into an array. I've removed that step for this example to demonstrate that isn't causing the anomaly.

'--worksheet code in Sheet1
Private Sub Worksheet_Calculate()
 Application.EnableEvents = False
 SyncFiltersByID
 Application.EnableEvents = True
End Sub

'--code in Module1
Sub SyncFiltersByID()
 Dim vVisibleItemList As Variant
 
 '--simplified for this example- will read Table1 in final code
 vVisibleItemList = Array("#111", "#333", "#444")

 '--sync Table2 to list of IDs in Table1
 With Sheet1.ListObjects("Table2").Range
   .AutoFilter Field:=1, _
      Criteria1:=Array(vVisibleItemList), Operator:=xlFilterValues
 End With

End Sub

If I enter any value in cell B21, Worksheet_Calculate is triggered and the code successfully filters Table2 to show ID's 111,222, and 444.

If I filter Table1, Worksheet_Calculate is triggered and the code unexpectedly applies the filters intended for Table2 to the ID field of Table1!

If I Cut and Paste Table2 into Sheet2 (and modify the code to reference it on Sheet2), when Table1 is filtered, the code successfully filters Table2 to show ID's 111,222, and 444.

The code will also work if I eliminate the Worksheet_Calculate and require the user to push a button to call SyncFiltersByID.

The best workaround I've found is to add a Slicer to the ID field of Table2, and sync the tables through the Slicer. I'll go with that unless someone can suggest a better solution. Note that the Tables do not use the same data source, so they can't be sync'd by slicers alone.

TIA

 

[Yongle]

Here's a tested workaround for you (cloak and mirrors approach)

Logic
- put Table2 on a different sheet
- worksheet_calculate no longer displays the behaviour described
- amend worksheet_calculate so that it syncs Table2 (in other sheet) before COPYING Table2 below Table1
- the copy looks like a table but is in fact a standard range
- if required the range could be converted to a table

To test
- put Table2 on another sheet and amend the references accordingly

'in Table1's sheet module
Private Sub Worksheet_Calculate()
    Dim r As Long
    Application.EnableEvents = False
[I][COLOR=#006400]'delete previous copy of Table2[/COLOR][/I]
    r = Me.ListObjects(1).Range.Offset(Me.ListObjects(1).Range.Rows.Count + 1).Row
    Me.Rows(r).Resize(1000).EntireRow.Delete 'deletes previous copy
[COLOR=#006400][I]'sync[/I][/COLOR]
    SyncFiltersByID
[COLOR=#006400]'copy Table2[/COLOR]
    [COLOR=#ff0000]Sheet99[/COLOR].ListObjects(1).Range.SpecialCells(xlCellTypeVisible).Copy Me.Range("A" & Rows.Count).End(xlUp).Offset(2)
    Application.EnableEvents = True
End Sub

'in Module1 (as before)
Sub SyncFiltersByID()
  Dim vVisibleItemList As Variant
   '--simplified for this example- will read Table1 in final code
  vVisibleItemList = Array("#111", "#333", "#444")
   '--sync Table2 to list of IDs in Table1
  With [COLOR=#ff0000]Sheet99[/COLOR].ListObjects(1).Range
     .AutoFilter Field:=1, Criteria1:=Array(vVisibleItemList), Operator:=xlFilterValues
  End With
End Sub

 

[Jerry Sullivan]

Yongle, Thanks for the idea.

That would accomplish the task, however I think that's more complicated than my current workaround of filtering Table2 using a slicer.

 

[Yongle]

After a lot of testing, it appears that running sync inside worksheet_calculate is the issue
- its a bug and the problem happens because there are 2 or more tables on the same sheet (it shouldn't but it does!)
- I have tried various things and (no matter what I try) that appears to be the stumbling block
- the macro runs correctly if it is run in any other way

Here is a simple alternative (which you may have already dismissed )

Delete worksheet_calculate and use this instead

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
       SyncFiltersByID
    Application.EnableEvents = True
End Sub

After filtering in Table1 simply click anywhere in in the sheet to sync
(or you could restrict it to clicking anywhere inside Table1 etc)

 

[Jerry Sullivan]

Yes, you are correct that I chose against using the SelectionChange event. I don't want to rely on, or impose upon the user that they select the worksheet in order to have the filters sync.

The workaround I'm using with the slicer is fine. I just didn't want that to be overkill if there was a missing some simple way to get autofilters to work.

Thanks again to you and Fluff for confirming that you experience the same unexpected behavior when running the OP code.