如何检测工作表上的过滤器是否更改？ [英] How to detect if filter(s) changed on a worksheet?

问题描述

在Excel工作簿项目中，我们如何检测一些工作表上的过滤器是否已经更新了？

解决方案

确保你有一个包含整个数据列的公式（例如， COUNT ）。如果是表格，请打开总计行。

更改过滤器时，Excel计算事件将因为公式而触发，您可以通过将以下代码插入到工作表中来进行提取。

  Private Sub Worksheet_Calculate （）
 
 MsgBoxCalculation
 
 End Sub 
  

您的工作表需要设计为只有数据，否则将需要代码来确定工作表上的计算事件是否由于过滤器更改而产生。

您将需要添加代码来获取过滤器值。关注 Filter 类成员，如Citeria1，Criteria2，Operator，On等。

In excel workbook project, how could we detect if the filters on some worksheet are updated?

解决方案

Make sure that you have a formula (e.g. COUNT) that includes an entire column of the data. In the case of a Table, turn on the Total row.

When the filter is changed, the Excel calculation event will fire because of the formula and you can pick this up by inserting the following code into the sheet.

Private Sub Worksheet_Calculate()

 MsgBox "Calculation"

End Sub

Your sheet will need to be designed to only have data, otherwise code will be needed to determine if the calculation event on the sheet did not originate due to a change in filter.

You will need to add code to pick up the filter values. Focus on the Filter class members like Citeria1, Criteria2, Operator, On, etc.

这篇关于如何检测工作表上的过滤器是否更改？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！