CODEWITHSUNDEEP
vbaexcel
teddy2
teddy2

Reputation: 390

It is possible to trap AutoFilter as an Event?

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?

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

Upvotes: 1

Views: 4253

Answers (2)

cyboashu
cyboashu

Reputation: 10433

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.

enter image description here

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.

Upvotes: 4

brettdj
brettdj

Reputation: 55682

The key points from my article Trapping a change to a filtered list with VBA

  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.

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

  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

Upvotes: 0

Related Questions