How to trap auto filter?

  • Thread starter Thread starter philip.ohnewein
  • Start date Start date
P

philip.ohnewein

Hi there,

I am wondering if it is possible to trap the user action that happens
if a user applies or changes an auto filter, i.e. the user selects a
specific autofilter in an autofilter cell (or the user selects
'all').

None of the usual worksheet events like Calculate, Change,... are
triggered.
Is there a possibility of trapping this user interaction?

Thanks for your help or suggestions!
Philip
 
Maybe you could add a formula like:
=subtotal(3,a2:a9999)
to an out of the way location.

The =subtotal() will reevaluate when the filter changes. Then you could tie
into the worksheet_calculate event. But I don't know a way of determining what
caused the recalc--a change in the filtering or a change to a cell that is used
in a formula????

ps. In xl2003, changing the filter criteria is enough to have excel
recalculate.
 
hi dave,

thanks for your suggestion! this seems to work, so thanks for this
workaround.
btw, i am using xl 2002, which does not trigger worksheet_calculate
upon a filter change.

so thanks again!
philip
 
Back
Top