Detecting when a table filter is changed

[SQUIDD]

Evening all

I have been searching this for a while tonight, with no luck.

I tried sheet_calcualte and chance events. It appears filtering a table does not trigger any of these.

In short, im trying to run a macro when a table filter changes to update some stats.

Obviously i can add a button for the user to call this, its just i think it happening automatically is better. If possible, maybe not.

Thanks

Dave

 

[kevin9999]

Please try the following.
Somewhere (anywhere) on your sheet with the table(s) put the following formula:

=COUNTA(A:A)

Then try using the Private Sub Worksheet_Calculate() event handler again.

 

[Alex Blakenburg]

I didn't find @kevin9999's count to trigger the Calculate Event but using that concept you could try using this to trigger it:

=SUBTOTAL(3,Table1[Col1])

 

[kevin9999]

That's strange @Alex Blakenburg , it certainly worked for me

Could you try the linked file & let me know what you get?
Cheers

Detect Filter Change.xlsm

 

[SQUIDD]

Hi Both

Really appriciate you looking at this.

Kevin, so i tried your approach, and yes, it works, thankyou. So long as i tied it to some of the data in the table range. The change event was triggered.

Alex, yours worked also.

Thanks both for your cool approach. And your time.

Dave

 

[kevin9999]

Glad we could help you, and thanks for the feedback

 

[Peter_SSs]

It possibly does not matter for your intended use but note that neither suggestion specifically detects when a table filter is changed per your thread title & post 1 description.
If you click one of the filter drop-downs and then click 'OK' without changing anything, the event is still triggered.

 

[SQUIDD]

Hi Peter.

Thanks for the heads up on that, in this case it actually does not matter.

Thanks all

Dave

 

[Peter_SSs]

Cheers.