Excel VBA过滤器更改事件处理程序 [英] Excel VBA Filter Change event handler

问题描述

有没有办法弄清楚用户何时更改纸张过滤器？

Is there a way to figure out when the user has made changes to the sheets filter?

换句话说，是否有一种 change_filter事件某种处理程序？

In other words is there a change_filter event handler of some sort?

推荐答案

是的。

从这个文章我发布在另一个论坛上

From this article I posted on another forum

1.A dummy WorkSheet添加了一个 SUBTOTAL A1中的公式返回到主页上过滤的范围。

2.一个 Worksheet_Calculate（）事件被添加到虚拟工作表中，当更改过滤器时， SUBTOTAL 公式更新时，此事件将触发。

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

请注意，上述方法需要设置工作簿计算自动（XA计算自动在VBA中）或自动除表（xlCalculationSemiAutomatic）。如果计算设置为手动（xlCalculationManual），则需要进一步编码才能将WorkBook设置为只有虚拟工作表将被设置为自动计算，所有其他已关闭计算的工作表。

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.

有一个很少使用的WorkSheet属性 EnableCalculation ，可以通过Visual Basic编辑器设置为True或False。默认设置显然为True，如果设置为False，则工作表将不会计算。

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.

EnableCalculation属性不适用于常规Excel菜单或功能区选项 - 因此，除了这可能是一个有用的技巧，为希望安全Excel模型的人

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. 添加一个 Workbook_Open 将Dummy以外的所有表格的 EnableCalculation属性设置为False的事件。


2. 在计算模式下运行工作簿。

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.

这篇关于Excel VBA过滤器更改事件处理程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！