Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

excel - VBA:Trigger macro on column filter

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

Please help

Thanks.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.

enter image description here

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:

enter image description hereenter image description here

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...