How to trap auto filter?

[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

 

[Dave Peterson]

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.

 

[philip.ohnewein]

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