Any way to capture an Autofilter event?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mrwaller - 20 Jan 2006 17:44 GMT

I want to execute some code whenever a filtering event has occurred
(selecting a value from the filter drop-down list). There are many events
that can occur on a worksheet (calculate, change, etc.) but none of them kick
off for a filter selection.

Reply to this Message

Bernie Deitrick - 20 Jan 2006 18:15 GMT

You can use the calculate event, but you need a formula that is dependent on the results of the
filter. Name a cell FormCell, and put a formula like:

=SUBTOTAL(9,B1:B100)

into it. Then name another cell ValueCell, and use code like this: you can replace the msgbox with
your code.

Private Sub Worksheet_Calculate()
If Range("FormCell").Value <> Range("ValueCell").Value Then
MsgBox "Filtered"
Application.EnableEvents = False
Range("ValueCell").Value = Range("FormCell").Value
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

>I want to execute some code whenever a filtering event has occurred
> (selecting a value from the filter drop-down list). There are many events
> that can occur on a worksheet (calculate, change, etc.) but none of them kick
> off for a filter selection.

Reply to this Message