Trapping the AutoFilter Event

  • Thread starter Thread starter PosseJohn
  • Start date Start date
P

PosseJohn

I have a worksheet that has the AutoFilter enabled.

Is there a method to trap when the user selects to perform the AutoFilter?

I want to carry out a few steps when they do.
 
Hi John

Try this from should get you what you want. Need to change the sheet
name and insert your actions.

Take care

Marcus

Sub autofil()

With Worksheets("Sheet1")
If .AutoFilterMode Then
'Your code here
End If
End With

End Sub
 
I don't see how that would work...if the user performs a AutoFilter action,
what would trigger the Sub AutoFil procedure?
 
Sorry misread your post.

This from the msdn website

"One of the limiting aspects of using the AutoFilter feature in a
business solution is that it has no directly associated event in the
Excel object model. In other words, when a filter is applied or
changed, by a user or programmatically, there is no explicit event for
the activity."

The article goes on to explain a way to capture the autofilter event
with the use of smart tags. Will let you get your head around it. It
is worth reading.

http://msdn.microsoft.com/en-us/library/aa140046(office.10).aspx

Take care

Marcus
 
You can use the Calculate Event to trap AutoFilter changes. For example:

=SUBTOTAL(2,A:A)

will give the count of visible cells in column A. Excel re-calculates this
whenever the AutoFilter is adjusted and the event gets triggered.
 
Back
Top