How to detect if filter(s) changed on a worksheet?

Question

In excel workbook project, how could we detect if the filters on some worksheet are updated?

Answer 1

Make sure that you have a formula (e.g. COUNT) that includes an entire column of the data. In the case of a Table, turn on the Total row.

When the filter is changed, the Excel calculation event will fire because of the formula and you can pick this up by inserting the following code into the sheet.

Private Sub Worksheet_Calculate()

 MsgBox "Calculation"

End Sub

Your sheet will need to be designed to only have data, otherwise code will be needed to determine if the calculation event on the sheet did not originate due to a change in filter.

You will need to add code to pick up the filter values. Focus on the Filter class members like Citeria1, Criteria2, Operator, On, etc.