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?
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.
Workbook_Open
Event to set the EnableCalculation property
of all sheets other than "Dummy" to False.