All geek questions in one place

Excel VBA Filter Change event handler

Is there any way to find out when the user made changes to the sheet filter?

In other words, is there any change_filter event handler?

+5
source share
1 answer

Yes.

From this article I posted on another forum

1. A dummy worksheet is added with one SUBTOTAL formula in A1, indicating the range filtered on the main sheet.
2. A Worksheet_Calculate() The event is added to the dummy WorkSheet, this event is fired when the SUBTOTAL formula SUBTOTAL updated when the filter changes.

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

Manual calculation power

Note that the above approach requires that the workbook calculator be set to either automatic (xlCalculationAutomatic in VBA) or automatic, except for tables (xlCalculationSemiAutomatic). If the Calculation parameter is set to Manual (xlCalculationManual), additional coding is required to encode the workbook so that only the "w320" WorkSheet is set to automatically calculate, all other sheets in which the calculation is disabled.

There is a rarely used WorkSheet property, EnableCalculation , which can be set using the Visual Basic Editor to True or False. The default value is True, if it is set to False, the worksheet will not calculate.

The EnableCalculation property is not available for the usual Excel menu or ribbon options - aside, this can be a useful trick for people who are looking for reliable Excel models by deliberately keeping key sheets from recounting.

  1. Add a Workbook_Open event to set the EnableCalculation property all sheets other than "dummy" to False.
  2. Run the book in calculation mode.
+7
source

Source: https://habr.com/ru/post/1215111/

More articles:


All Articles