9

Is there a way to figure out when the user has made changes to the sheets filter?

In other words is there a change_filter event handler of some sort?

Community
  • 1
  • 1
behnam
  • 159
  • 1
  • 1
  • 7

2 Answers2

15

Yes.

From this article I posted on another forum

1.A dummy WorkSheet is added with a single SUBTOTAL formula in A1 pointing back to the range being filtered on the main sheet.
2. A Worksheet_Calculate() Event is added to the dummy WorkSheet, this Event fires when the SUBTOTAL formula updates when the filter is changed.

'Dummy sheet code
Private Sub Worksheet_Calculate()
'Dummy Sheet has recalculated
    MsgBox "Your list has been filtered"
End Sub

Catering for Manual Calculation

Note that the approach above requires Workbook Calculation to be set to either Automatic (xlCalculationAutomatic in VBA), or Automatic except tables (xlCalculationSemiAutomatic). If Calculation was set to Manual (xlCalculationManual), further coding is necessary to set the WorkBook up so that only the "dummy" WorkSheet would be set to automatically Calculate, all other sheets having Calculation turned off.

There is a rarely used WorkSheet property, EnableCalculation, that can be set via the Visual Basic Editor to True or False. The default setting is obviously True, if it is set to False then the worksheet will not calculate.

The EnableCalculation property is not available to the regular Excel Menu or Ribbon options - so as an aside this can be a useful trick for people who are looking to secure Excel models by deliberately keeping key sheets from recalculating.

  1. Add a Workbook_Open Event to set the EnableCalculation property of all sheets other than "Dummy" to False.
  2. Run the Workbook in Calculation mode.
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • On the "article" link above, I cannot find your "Worksheet_Calculate()" method. Can you please give me a better link or explain better where to find it ! Thank you ! – iKK Jun 22 '15 at 15:20
  • Thanks - I managed by reading again and again :) It works but is a bit on a workarround, don't you think ? Is there a "more clean" way really detecting Autofilter-events ?? – iKK Jun 22 '15 at 15:55
  • @skuenstler I think the article has been deleted - will see if I can get it restored. Nope, the reason the workaround is needed is because there isn't a direct route :) – brettdj Jun 23 '15 at 02:23
  • @brettdj Thanks for the helpful answer above. I am finding that saving a workbook will also then trigger this calculation event. Was that the case in your example please? – QHarr Feb 06 '18 at 08:09
  • @brettdj, It looks like the EE article you are referencing is about another topic written by someone else. The technique you describe on this page was enough for me to get it working but it's always nice to have a link to more in-depth knowledge. – Ben May 02 '19 at 13:32
2

This is a more detailed version of this hidden gem answer. Posting here for more visibility.

  • Works even if Calculation is set to Manual.

  • Assumes you have a chart using the table being filtered as its data source.
    If not, you'll need one.

  1. Create a standard module called ChartSubscriber.
Private ChartEvents As New ChartEvents

Sub SubscribeToChartEvents()
  Set ChartEvents.Chart = Worksheets("Sheet with Chart").ChartObjects("Chart Name").Chart
End Sub
  1. Create a class module called ChartEvents.
Public WithEvents Chart As Chart

Private Sub Chart_Calculate()
    Debug.Print "Table was filtered. Do your worst!"
End Sub
  1. In the ThisWorkbook module subscribe to the event on Workbook_Open.
Private Sub Workbook_Open()
    Call ChartSubscriber.SubscribeToChartEvents
End Sub
GollyJer
  • 23,857
  • 16
  • 106
  • 174