Filter event in Excel VBA? 2

[FractalWalk]

I want to trigger a macro everytime a user changes the filter criteria on a spreadsheet. When using a filter, none of the data in the cells actually changes, therefore thee is no SheetChange event.

Is there a filter(change) event in VBA?

 

[Zathras]

It isn't foolproof, but if you have a formula like this:

AC1: =SUBTOTAL(9,C2:C5000)

where the filter range is rows 1 thru 4999 (in this example) and column C (again, just for this example) contains numbers such that the sum will be different for each filter selection, then you can trap the Calculate event with a macro like this:

Option Explicit
Dim LastSum As Double

Private Sub Worksheet_Calculate()
  If [AC1] <> LastSum Then
    MsgBox &quot;filter changed&quot;
    LastSum = [AC1]
  End If
End Sub

You can put the SUBTOTAL formula anywhere on the worksheet. Put it in a hidden column, if you like. Or set the font for white on white to make it "disappear."

 

[FractalWalk]

Zathras:

Thanks! Trapping the calculate event is exactly what I need to do.

 

[xlbo]

FractalWalk - just as a pice of info, the way to say thanks in TekTips is to award a star - you can do this by clicking on the "mark this post as a helpful / expert post" on the appropriate post. This also means that if anyone is searching the archives for a similar question, they can easily see which threads have yielded a satisfactory answer...

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde