CODEWITHSUNDEEP
vbaexcel
Jae Carr
Jae Carr

Reputation: 1225

Listen for Table Filter Activation

I have a table that has a series of filters across the top of it (as per usual.) This table feeds a massive graph that has multiple series of data in it. In order to hide different series of data I have grouped the information in the tables. I can then click on the minus button to remove a series of data from the graph by collapsing the group, or click the plus button to add it back in the same way. Yes, I know, not the best solution. It was an early effort, what can I say? My default is for all data to be hidden (all groups collapsed).

I have a user (a very important user) who wants to be able to use the filters across the top to find the specific series they are looking for. The filter will find the collapsed series just fine, but the series will not display because it is collapsed.

So, What I'm trying to do is get all groups to auto-expand when the filter is activated. This should work as a stop gap measure until I can simply redesign the chart.

The Problem: I can't figure out how to make Excel notice when the filter has been clicked on (or otherwise used). I've tried using Worksheet_Change and Worksheet_SelectionChange, but neither of them activate the code I have set up in the listener. That code, FYI, checks to make sure the filter is in use and adjusts the groupings accordingly. It should work fine if I can just get Excel to notice it's existence.

I've looked into making my own listener, but there's nowhere in the code I can insert it to make it activate. I just need a listener that will notice when the filter has been changed.

Any thoughts? After an hour of searching I'm stumped...

Upvotes: 1

Views: 485

Answers (2)

Jae Carr
Jae Carr

Reputation: 1225

Okay, after some research I figured out a work around. The big problem here is that changing a filter does not raise any events that can be heard by VBA. Big problem.

Simple Solution: Create something that will activate a listener.

What I ended up doing was finding a cell somewhere outside of my table that wasn't going to be affected by the collapses, then I added a very simple formula (=Count(H:H)). Now whenever the table is collapsed the count is affected which activates the Worksheet_Calculate listener. And voila! I can dynamically change the groupings all I want :-).

So there you have it. If you need to detect a filter being activated via a Worksheet listener, you just need to set up a formula to activate the calculate listener.

Reference: [MSDN Article on the same thing].1 There is apparently a much more robust way to fix this problem as well which is detailed in the article.

Upvotes: 4

bonCodigo
bonCodigo

Reputation: 14361

  • You mentioned table, so assuming it's PivotTable you may try,

Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

  • If it's simply a Targe Range change then,

If Intersect(Target, Range("A2"))

Upvotes: 1

Related Questions