检测自动过滤器更改 [英] Detect Autofilter changes

问题描述

我正在寻找检测特定表上自动过滤器的任何更改的目的，目的是强制UDF对当前可见的表条目执行一些简单的算术运算以更新其结果.到目前为止，使UDF易失性没有任何影响，但也没有直接针对该表.

此解决方案( https://stackoverflow.com/a/15906275/4604845 )对我不起作用，可能是因为我只有手动计算.

我的一个非常肮脏的解决方法是 Workbook_SelectionChange ，虽然UDF不会以这种方式杀死资源消耗，但是如果数据变大，我宁愿避免这种情况.另外，在使用计算机方面，我有一些用户是新手，我很难相信我可以让他们所有人都明白，他们需要在更新自动筛选器后单击其他内容才能使结果正确.

有什么想法吗?我已经尝试过 Workbook_Change 和 Workbook_Calculate ，但是它们都没有被自动过滤器更改触发(或者我不知道如何触发它们).

解决方案

即使工作表上没有其他公式，如果在引用表的表上的某处包括 Subtotal()公式， Subtotal()会在每次更改自动过滤器时重新计算.

您可以使用它来触发 Calculate()事件宏.

EDIT#1:

假设我们在名为 data 的工作表的 A 列上设置了自动过滤器.表格 data 还包含许多其他公式.如果我们在 data 工作表中使用 Calculate()事件，则只要重新计算这些公式的任何，就会触发火灾.

我们创建一个名为 trigger 的新工作表.除了包含以下内容的单个单元格之外，该新工作表完全为空:

  = SUBTOTAL(3，data！A1:A20) 

在 trigger 工作表中，我们放置了 Calculate()事件宏.

现在，如果我们使用的是 data 工作表，则可以进行任意更改并执行各种重新计算，并且不会触发任何操作，但是如果更改了AutoFilter，则触发 trigger 上的事件宏>将看到变化并触发！

I am looking to detect any changes in an autofilter on a specific table, for the purpose of forcing a UDF that does some simple arithmetic on the table entries that are currently visible to update its result. Making the UDF volatile has had no impact so far, but it doesn't target the table directly either.

This solution (https://stackoverflow.com/a/15906275/4604845) does not work for me, probably because I have only manual calculation.

My a-little-too-dirty workaround is Workbook_SelectionChange, and while the UDF doesn't kill resource consumption this way, I'd rather avoid it in case the data grows larger. Also I have some users that are complete novices in terms of using computers, and I have trouble being confident that I can get all of them to understand that they need to click something else after updating the autofilter for the result to be correct.

Any ideas? I have tried Workbook_Change and Workbook_Calculate but none of them are triggered (or I can't figure out how to trigger them) by the autofilter changes.

解决方案

Even if you have no other formulas on the worksheet, if you include a Subtotal() formula somewhere on the sheet referencing the table, the Subtotal() will recalculate every time the autofilter is changed.

You can use this to trigger a Calculate() event macro.

EDIT#1:

Say we have an AutoFilter set on column A of a sheet named data. Sheet data also contains many other formulas. If we use the Calculate() event in the data worksheet, we will get fires any time any of these formulas re-calculate.

We create a new worksheet called trigger. This new worksheet is comletely empty except for a single cell that contains:

=SUBTOTAL(3,data!A1:A20)

It is in the trigger worksheet that we place the Calculate() event macro.

Now if we are using the data worksheet, we can make arbitrary changes and perform various recalculation and nothing fires, but if we change the AutoFilter, the event macro on trigger will see the change and fire!

这篇关于检测自动过滤器更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！