Run VBA code automatically after running a filter

问题:

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?

回答1:

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

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.

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

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

回答2:

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)

回答3:

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()