Run VBA code automatically after filter starts

Question

I have a written code that classifies employees along with their qualifications. To weed out employees with unwanted qualifications, I applied a filter to each column that named their qualification category.

I wrote VBA code so that duplicate names and qualifications are invisible for the convenience of the place. However, I cannot get the code to run automatically.

Currently, the only way to get the code to run is to install it

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

I found what I think is the right solution:

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

But I can’t figure it out.

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

Answer 1

Key Points from My Article Tracking Changes in a Filtered List Using VBA

The article in more detail and an example file, key points are given below

• A "dummy" SUBTOTAL is added using one SUBTOTAL formula in A1 , indicating the range filtered on the main sheet.
• A Worksheet_Calculate() An event is added to the "dummy" worksheet, this event fires when the SUBTOTAL formula SUBTOTAL updated when the filter changes.

The following two settings are required if you want to perform workbook calculation as Manual

• Add the Workbook_Open event to set the EnableCalculation property for all sheets other than "dummy" to False.
• Run workbook in billing mode

Answer 2

The ozgrid code that you mentioned tells you that you can put your code in the worksheet_calculate event (in the worksheet) for how long since you have something that will be recounted when your autofilter changes. This may be an intermediate formula that you can hide on your sheet, for example. =subtotal(3,A:A)

Answer 3

You still need to explore, but it looks like this: the Calc Calculate event is fired when calculating = xlCalculationManual. At least it works in my Excel 2007. So, the steps are:

• create a chart (saying "Chart 1" on Sheet1) that actually uses data from any column in the table
• make sure it updates the image when changing the filter

• create a new class, for example. 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 the filter and you should appear in Sub Chart_Calculate ()