VBA：在列过滤器上触发宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15904230/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA:Trigger macro on column filter
提问by user2144293
Is there a way we can trigger a macro function on column filter in excel??
有没有办法在excel中的列过滤器上触发宏函数？？
Please help
请帮忙
Thanks.
谢谢。
回答by Kazimierz Jawor
I was just thinking if I can post this answer. I guess some of you will not like it as it is not direct answer by presentation of bypass solution. However I think I can show that idea as we don't have all project assumptions in the question.
我只是在想是否可以发布此答案。我想你们中的一些人不会喜欢它，因为它不是通过提供旁路解决方案来直接回答的。但是，我认为我可以展示这个想法，因为我们在问题中没有所有的项目假设。
Let's agree- we all know that there is no event which fires after we change filtering. However, I see one option.
让我们同意 - 我们都知道在我们更改过滤后不会触发任何事件。但是，我看到了一种选择。
Changing filter could fire Worksheet_Calculateevent (not Worksheet_Change). If there is any single formula within your sheet than we will fire that event each time we change filtering criteria using our mouse.
更改过滤器可能会触发Worksheet_Calculate事件（不是Worksheet_Change）。如果您的工作表中有任何单个公式，那么每次我们使用鼠标更改过滤条件时，我们都会触发该事件。
Step 1.put any single formula in the sheet, like in cell ZZ1 where =ZZ2
步骤 1.将任何单个公式放入工作表中，例如在单元格 ZZ1 中，其中 =ZZ2
Step 2.I assume that our data range starts in Range(A1) and we have titles in first row (see the picture). I assume also there is nothing below that area.
第 2 步。我假设我们的数据范围从 Range(A1) 开始，并且我们在第一行中有标题（见图）。我认为该区域下方也没有任何东西。
Step 3.Put that following solution in Sheet1 module.
步骤 3.将以下解决方案放入 Sheet1 模块中。
Private Sub Worksheet_Calculate()
If ActiveSheet.Name = "Sheet1" Then
If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
MsgBox "No data available"
Else
MsgBox "There are filtering results"
End If
End If
End Sub
Step 4.Using filter would fire that event and result with following situations:
步骤 4.使用过滤器会触发该事件并导致以下情况：
I hope someone will like it and can use that. Even if it's only a bypass idea.
我希望有人会喜欢它并可以使用它。即使这只是一个绕过的想法。
