运行过滤器后自动运行 VBA 代码

Run VBA code automatically after running a filter

提问by user1732946

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.

我编写了我的 VBA 代码，以便使重复的名称和资格不可见，以便于定位。但是，我无法让代码自动运行。

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.

Private Sub Worksheet_Change(ByVal Target As Range) 然后更改任意单元格的值。

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

我在以下位置找到了我认为正确的解决方案：

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

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?

有没有办法运行此代码而不必在过滤器运行后选择和取消选择单元格？

回答by brettdj

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

我的文章使用 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 SUBTOTALformula in A1pointing 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 SUBTOTALformula updates when the filter is changed.

1. 添加了一个“虚拟”工作表，其中包含一个SUBTOTAL公式，用于A1指向主工作表上正在过滤的范围。
2. 一个Worksheet_Calculate()事件被添加到“虚拟”工作表中，SUBTOTAL当过滤器更改时公式更新时会触发此事件。

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

如果需要运行工作簿计算，则需要接下来的两个 setps Manual

1. Add a Workbook_OpenEvent to set the EnableCalculationproperty of all sheets other than "Dummy" to False.
2. Run the Workbook in Calculationmode

1. 添加一个Workbook_Open事件以将EnableCalculation除“Dummy”以外的所有工作表的属性设置为 False。
2. 在计算模式下运行工作簿

回答by nutsch

The ozgrid codeyou mentioned tells you that you can put your code in a worksheet_calculateevent (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)

你提到的ozgrid 代码告诉你，你可以把你的代码放在一个worksheet_calculate事件中（在工作表模块中），只要你有一些在你改变自动过滤器时会重新计算的东西。这可以是您可以隐藏在工作表中的小计公式，例如=subtotal(3,A:A)

回答by ilya

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:

仍然需要调查，但看起来 Chart Calculate 事件是在 Calculation = xlCalculationManual 时触发的。至少适用于我的 Excel 2007。所以步骤是：

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

• 创建一个图表（在 Sheet1 上说“图表 1”），它实际上使用来自任何表格列的数据
• 检查它是否在更改过滤器时更新其图片

• 创建一个新类，例如 clsChartEvents：

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

• 将此代码添加到某个模块或类：

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

• 执行 SubscribeToChartEvents
• 更改过滤器，您应该出现在 Sub Chart_Calculate() 中