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]

Use something to give you control over worksheet_calculate

Option1 - Simplest way is to ask the user

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
       If MsgBox("Sync?", vbYesNo) = 5 Then SyncFiltersByID
    Application.EnableEvents = True
End Sub

Option2 - Use a boolean variable
I have not refined this but have tested in principle and it does work

1. Add boolean variable at top of module1

Public [COLOR=#ff0000]Trigger[/COLOR] As Boolean
Sub SyncFiltersByID()
'your macro
End Sub

2. Use that to control worksheet_calculate
In sheet1's module

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
       If Not [COLOR=#ff0000]Trigger[/COLOR] = False Then SyncFiltersByID
    Application.EnableEvents = True
End Sub

3. Use something to flip the value between TRUE and Fale
- this is the tricky bit

My first thought was something along these lines ....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Trigger = True
    If Not Intersect(Target, Me.ListObjects("Table2").Range) Is Nothing Then Trigger = False
    If Not Intersect(Target, Me.ListObjects("Table1").Range) Is Nothing Then Trigger = False
End Sub

But this only works if a cell in either table is selected BEFORE the table is filtered
- filtering does not trigger selection change

Perhaps you can think of a more appropriate trigger

 

[Yongle]

A development on using the Boolean variabble Tigger

To get around filter not triggering anything
- use an active-x label and use its MouseMove event
- I have tested the method and it works

How?
- Add an active-x label and make it the same width as Table1 and the same height as header row
- Place it over the header ow
- (Later you can make it transparent and remove text etc - leave visible whilst testing!)
- Use MouseMove event to move the Label away(allowing tablle to be manually filtered) and sets Trigger to FALSE
- Filtering the table triggers worksheet_calculate but Sync macro does not run
- worksheet_calculate moves the label back over the table and sets Trigger to TRUE

You may want to replicate for Table2

Code
In sheet1's module

Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
[I][COLOR=#008080]'move label away and set boolean variable[/COLOR][/I]
    Label1.Left = Me.ListObjects("Table1").Range(1).Left + 500
    Trigger = False
End Sub

Private Sub Worksheet_Calculate()
[I][COLOR=#008080]'use boolean to determine if other macro runs[/COLOR][/I]
    Application.EnableEvents = False
       If Trigger Then SyncFiltersByID
    Application.EnableEvents = True
[I][COLOR=#008080]'place label over headings and reset boolean[/COLOR][/I]
    Label1.Left = Me.ListObjects("Table1").Range(1).Left
    Label1.Top = Me.ListObjects("Table1").Range(1).Top
    Trigger = True
End Sub

In module1

Public Trigger As Boolean
Sub SyncFiltersByID()
 your macro
End Sub

 

[Jerry Sullivan]

Hi Yongle,

Thank you for all the thought you put into this.

Were you able to replicate the problem that I described? Using my original code, if I manually filter Table1 then this part of the code applies filters to Table1 instead of Table2...

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

The suggestions that you offered are creative, but after the added confirmation/trigger toggle steps, they still run the same line of autofilter code where the problem of filtering the wrong table occurs.

It's possible that this is a problem specific to my version/build of Excel. I hadn't considered that, so I'd appreciate very much if you can check to see if you get this same anomaly I described when running the code in my OP.

 

[Yongle]

I must have misunderstood

Exactly what is the problem you are trying to fix?

 

[Jerry Sullivan]

My OP wasn't as clear as it could have been.

The code is supposed to filter Table2 when Table1 is filtered. It doesn't. It applies additional filters to Table1.

 

[Fluff]

I get exactly the same as you, using 2013.
Also if I switch the code to filter table1 & manually filter table2, then table2 filters get changed not table1

 

[Jerry Sullivan]

I get exactly the same as you, using 2013.
Also if I switch the code to filter table1 & manually filter table2, then table2 filters get changed not table1

Hi Fluff, Thank you for confirming that you get the same behavior with xl2013.

 

[Fluff]

Just been playing around with it & using this

   Set Tbl = Sheet22.ListObjects("Table2")
   With Tbl.Range
      Debug.Print .Address
      .AutoFilter 1, Array(vVisibleItemList), xlFilterValues
   End With

It gives me the correct range for Table2, but still filters Table1

 

[Jerry Sullivan]

Just been playing around with it & using this

   Set Tbl = Sheet22.ListObjects("Table2")
   With Tbl.Range
      Debug.Print .Address
      .AutoFilter 1, Array(vVisibleItemList), xlFilterValues
   End With

It gives me the correct range for Table2, but still filters Table1

Yes, I had tested that too.

I also tried adding a statement to Select a cell in Table2 prior to the filter statement, thinking that maybe Excel couldn't filter the table if another table was selected. That didn't work either and Table1 was still filtered.