Excel VBA Filter Change event handler

by

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.

More Related Contents:

  1. Save excel file multiple times with file names in another workbook.
  2. Sequence of numbers based on start and end value of 2 cells [closed]
  3. How do I find the last column with data?
  4. Better way to find last used row
  5. How to insert a picture into Excel at a specified cell position with VBA
  6. 2 Dimensional array from range [closed]
  7. Save multiple sheets to .pdf
  8. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  9. How can I create a calendar input in VBA Excel?
  10. Using Google Chrome in Selenium VBA (Installation Steps)
  11. Dir() function not working in Mac Excel 2011 VBA
  12. Merge values of column B based on common values on column A
  13. How to use workbook.saveas with automatic Overwrite
  14. How to import a table from web page (with “div class”) to excel?
  15. Range limit conundrum
  16. How do you run a .exe with parameters using vba’s shell()?
  17. Excel VBA – Range.Copy transpose paste
  18. How to determine the last Row used in VBA including blank spaces in between [duplicate]
  19. Passing variable from Form to Module in VBA
  20. How to give a time delay of less than one second in excel vba?
  21. Changing the date format to yyyy-mm-dd
  22. Delete all duplicate rows Excel vba
  23. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  24. If string contains word from list, return value adjacent to list value
  25. every possible combination of the contents of 2 columns in excel
  26. excel vba freeze pane without select
  27. Converting a string representation of a constant into a constant?
  28. Access files with long paths (over 260)
  29. How to highlight selected text within excel
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment