是否可以将“自动筛选"捕获为事件? [英] It is possible to trap AutoFilter as an Event?

问题描述

我正在尝试向报表中的用户显示过滤器列.Excel给出了一个不同的图标，但是没有.最好用其他颜色(例如蓝色)为列着色.

我在

然后，转到工作簿的代码并添加以下内容:

  Private Sub Workbook_SheetCalculate(ByVal Sh作为对象)呼叫markFilter(Sh)MsgBox过滤器已更改"结束子 

景气.现在，您正在捕获过滤器更改事件，它将通过触发vba代码来更新过滤后的列.

注意 markFilter 来自您提到的答案.

I am trying to show the filter columns to users in the report. Excel gives a different icon but for large no. columns it will be good to color the columns in another color like blue.

I found code at Is there a way to see which filters are active in Excel, other than just the funnel icons?

• It works for me, but how do start this code without any button
• SheetChange and selection change do not work.

code

Sub test()
Call markFilter(ActiveSheet)
End Sub


Sub markFilter(wks As Worksheet)

    Dim lFilCol As Long

    With wks
        If .AutoFilterMode Then
            For lFilCol = 1 To .AutoFilter.Filters.Count

                '/ If filter is applied then mark the header as bold and font color as red
                If .AutoFilter.Filters(lFilCol).On Then
                    .AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Color = vbRed
                    .AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Bold = True
                Else
                     '/ No Filter. Column header font normal and black.
                    .AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Color = vbBlack
                    .AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Bold = False
                End If
            Next
        Else
            '/ No Filter at all. Column header font normal and black.
            .UsedRange.Rows(1).Font.Color = vbBlack
            .UsedRange.Rows(1).Font.Bold = False
        End If
    End With
End Sub

解决方案

I will use the same example I used in the answer that you mentioned in your post. I answered that. :)

There is no filter change event in excel. One work-around that I would use is trapping the calculate method of the worksheet or better the workbook.

So, in the worksheet with filter add a dummy formula like this: =SUBTOTAL(3,Sheet2!$A$1:$A$100) this counts only visible cells. But its up to you. Feel free to use any formula that responds to filter change.

After that, go to workbook's code and add this :

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
     Call markFilter(Sh)
     MsgBox "Filter changed"  
End Sub

Boom. Now you are trapping the filter change events and it will update the filtered columns by firing the vba code.

Note markFilter is coming from the answer that you mentioned.

这篇关于是否可以将“自动筛选"捕获为事件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！