运行VBA code运行的过滤器后自动 [英] Run VBA code automatically after running a filter

问题描述

我有写了一个code与他们一起资格的员工进行分类。为了剔除与不需要资格的员工我已经应用了过滤器的每个列标题从业资格的类别。

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.

我写我的VBA code，以便重复的姓名和资历都便于位置可见。但是，我无法得到code自动运行。

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.

目前我可以得到code运行的唯一方法是将其设置为

Currently the only way I can get the code to run is by setting it to

私人小组Worksheet_Change（BYVAL目标作为范围），然后更改任意单元格的值。

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

我发现了我认为是在正确的解决方案：

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

http://www.ozgrid.com/forum/showthread.php？ T = 72860

但我无法理解它。

有没有一种办法，而不必选择和过滤器运行后取消选择一个单元上运行该code？

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

推荐答案

这是我的文章的要点<一个href=\"http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2773-Trapping-a-change-to-a-filtered-list-with-VBA.html\"相对=nofollow>补漏更改的过滤列表使用VBA

有更详细资料，以及文章的示例文件，要点概括如下

There is more detail and a sample file with the article, the key points are summarised below

1. 虚拟工作表中添加了一个小计公式中的 A1 指回范围内被过滤在主表。


2. A Worksheet_Calculate（）事件被添加到虚拟的工作表，此事件在该小计配方更新时，过滤器被改变。

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.

如果希望将运行工作簿计算在需要接下来的两个setps 手动

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

1. 添加 Workbook_Open 事件设置比虚拟为False之外的所有表的 EnableCalculation 属性。


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

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