VBA：在列过滤器上触发宏 [英] VBA:Trigger macro on column filter

问题描述

有没有办法可以在excel中的列过滤器上触发宏函数？

请帮助

谢谢。

解决方案

我只是在想，如果我可以发布这个答案。我想你们中的一些人不会喜欢它，因为它不是直接回答旁路解决方案。不过，我认为我可以将这个想法显示为，我们在问题中没有所有的项目假设。

我们同意 - 我们都知道改变过滤后，没有任何事件发生。但是，我看到一个选项。

更改过滤器可能会触发 Worksheet_Calculate 事件（而不是 Worksheet_Change ）。如果您的工作表中有单个公式，我们将每次使用鼠标更改过滤条件时触发该事件。

步骤1。将任何单个公式放在工作表中，如单元格ZZ1其中= ZZ2

步骤2。我假定我们的数据范围以范围A1），我们在第一行有标题（见图）。我假设在该区域以外没有什么。

步骤3。将以下解决方案放在Sheet1模块中。

如果ActiveSheet.Name =Sheet1然后
如果单元格（Rows.Count，1）.End（...） xlUp）.Row = 1然后
MsgBox无可用数据
Else
MsgBox有过滤结果
结束如果
结束如果
结束Sub

步骤4。使用过滤器将触发该事件并产生以下结果情况：

我希望有人会喜欢它，可以使用它。即使这只是一个旁观者的想法。

Is there a way we can trigger a macro function on column filter in excel??

Please help

Thanks.

解决方案

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_Calculate event (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.

Step 1. put any single formula in the sheet, like in cell ZZ1 where =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.

Step 3. Put that following solution in Sheet1 module.

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:

I hope someone will like it and can use that. Even if it's only a bypass idea.

这篇关于VBA：在列过滤器上触发宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！