How to detect the event: autofilter in use

[Anders]

Hi!
Directly after the use of auto filter on different columns I want
functions in Vba to be activated and perform calculations with the
remaining data.
How can I recognise the that filtering event has occurred?

Thanks!

Anders

 

[Frank Kabel]

Hi Anders

IMO this can't be done. You're not able to detect automatically if a
user has invoked an autofilter. The worksheet_change event is not
triggered. Ideas for workarounds:
1. Create your own filter buttons with your specific code
2. Use the OnTime method to periodically check the Filtermode of the
ActiveSheet (but this could really slow down your spreadsheed - never
tested it). Have a look at http://www.cpearson.com/excel/ontime.htm
for instructions how to use OnTime

Maybe someone else has a better solution.
HTH
Frank

 

[Dave Peterson]

This might work for you, but you need some setup stuff:

1. You can't be in manual calculation mode

2. You have to have a formula that calculates when the filter is changed.
Maybe something like:
=subtotal(3,A2:A999)
(counta against the visible rows in the filter)
(or even =rand() in a cell that's out of the way)

3. and some code in the worksheet_calculate event:

Option Explicit
Private Sub Worksheet_Calculate()
With Me
If .AutoFilterMode Then
If .FilterMode Then
'your code here
'or your call to the other macro here
MsgBox "I'm filtered!"
End If
End If
End With
End Sub

(Rightclick on the worksheet tab that you want. Paste this in the
code window and then back to excel to test it out.)

4. This gets called each time the worksheet gets recalculated--not just
when the filter gets changed--so that could be the stopper.