Call macro when filter is applied?

**JP Romano** · 06-23-2011, 04:18 PM

Is it possible to kick off a macro when a filter is applied? User will have about 10 columns of data, and would like to call specific macros when each of these columns are filtered. The sheet doesn't exist yet - so I suppose this is more of a theoretical question

Thanks for any input!

**MarvinP** · 06-23-2011, 04:43 PM

Hi JP Romano,

If the filter is applied uising a Table and the top row of the table doesn't move, you can use Event Macros to trigger a macro.

Say the Table Filters are from A1 to H1.

Code behind the Worksheet that would look like this:

Please Login or Register  to view this content.

**Paul** · 06-23-2011, 04:47 PM

Hi JP,

There isn't a specific event trigger for using the AutoFilter, however you could try using the Worksheet_Calculate event if your table has formulas in it. If there are no formulas, in an unused cell just put the =NOW() function, which will force a recalc every time you change the filter(s).

JP Gonzalez has a thread to determine which column is filtered here: http://www.mrexcel.com/forum/showthread.php?p=664446

So in the Worksheet_Calculate event you could use that code to check which column is filtered, and then call the appropriate macro.

**Paul** · 06-23-2011, 04:49 PM

Marvin, if I'm not mistaken, applying a filter to a column does not change the activecell, therefore that event is not triggered.

**MarvinP** · 06-23-2011, 05:22 PM

Paul,

You are absolutely correct. But to live up to my signature line, find the attached that shows how to test that.

I hope the attached will allow the OP to discover how to test triggers. AND I was wrong about applying a Filter triggering an event.

**JP Romano** · 06-24-2011, 07:54 AM

Unfortunately, there are no calculations anywhere on the sheet. However, you did just give me the idea to add a subtotal(counta) formula, to the sheet.. then whenever a filter is applied, that count will change, and I can kick something off when that cell changes.
Thanks all!!!!!