Run VBA code automatically after running a filter

Question

I've got a code written that categorizes employees along with their qualifications. In order to weed out employees with unwanted qualifications I have applied a filter to each column that titles the category of their qualification.

I've written my VBA code in order that repetitious names and qualifications are made invisible for ease of location. However, I am unable to get the code to run automatically.

Currently the only way I can get the code to run is by setting it to

Private Sub Worksheet_Change(ByVal Target As Range) and then changing the value of an arbitrary cell.

i found what I believe to be the correct solution at:

http://www.ozgrid.com/forum/showthread.php?t=72860

But I cannot make sense of it.

Is there a way to run this code without having to select and deselect a cell after the filter has run?

Please upload and link to a a worksheet so we can see what you are talking about. — Matt Ridge, Oct 09 '12 at 20:38

brettdj · Answer 1 · 2014-04-19T01:40:09.650

4

The key points from my article Trapping a change to a filtered list with VBA

There is more detail and a sample file with the article, the key points are summarised below

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

The next two setps are needed if it is desired to run the Workbook Calculation as Manual

Add a Workbook_Open Event to set the EnableCalculation property of all sheets other than "Dummy" to False.
Run the Workbook in Calculation mode

edited Apr 19 '14 at 01:40

answered Oct 10 '12 at 00:00

brettdj

54,857
16
114
177

Good article, Brett. I put together a test file (the question was answered while I was doing this) and found that the Worksheet_Calculate event was called multiple times, is this normal behaviour? – mkingston Oct 10 '12 at 00:37
@brettdj - the linked article is deleted :-( – Peter Albert Jul 06 '15 at 19:08
1

@PeterAlbert I have requested it be undelted - will follow up again. – brettdj Jul 07 '15 at 00:35

score 1 · Answer 2 · answered Oct 09 '12 at 21:52

The ozgrid code you mentioned tells you that you can put your code in a worksheet_calculate event (in the worksheet module), as long as you have something that will recalculate when you change your autofilter. This something can be a subtotal formula that you can hide in your worksheet, e.g. =subtotal(3,A:A)

score 1 · Answer 3 · answered Sep 30 '14 at 18:54

Still need to investigate but looks like Chart Calculate event is triggered when Calculation = xlCalculationManual. At least works on my Excel 2007. So the steps are:

create a chart (saying "Chart 1" on Sheet1) which actually uses data from any of your table column
check that it updates its picture when you change the filter

create a new class e.g. clsChartEvents:

Public WithEvents Chart As Chart
Private Sub Chart_Calculate()
  Stop
End sub

add this code to some module or class:

Private chartEvents as new ChartEvents 'create a module-scope variable
sub SubscribeToChartEvents
  set chartEvents.Chart = Sheet1.ChartObjects("Chart 1").Chart
end sub

execute SubscribeToChartEvents
change a filter and you should appear in Sub Chart_Calculate()

This is a hidden gem. Thanks! [I provided a more detailed version in a similarly worded question](https://stackoverflow.com/a/64950286/25197) to bring more visibility. — GollyJer, Nov 22 '20 at 03:09

Run VBA code automatically after running a filter

3 Answers3

Linked