Display Msgbox for the Columns filtered instead of formatting cells.

[jonnathanjons]

Hello Experts,
I have a script which Highlights what are the columns which are filtered by changing the format of the column header. I found this is tampering my actual format of the report so I've to change the format back to its Original ever time I run this scrip. Can you please enrich the script to Display the Column number in a MsgBox instead of formatting the cells

I have posted this in another forum but did not get any reply yet..
https://chandoo.org/forum/threads/displ ... lls.52530/

Code:

Code: Select all

Option Explicit
Function DisplayFilter() As String
Dim i As Long
Dim ws As Worksheet

Set ws = ActiveSheet
If Not ws.FilterMode Then
    DisplayFilter = "No Filter"
    Exit Function
End If
For i = 1 To ws.AutoFilter.Filters.Count

    If ws.AutoFilter.Filters(i).On Then
        'ws.Cells(1, i).Interior.Color = vbRed   '<<<< Need help here to show MsgBox'
        ws.Cells(1, i).Font.Color = vbRed
        ws.Cells(1, i).Font.Bold = True
    End If
Next i

End Function

Sub ShowFilter()
Call DisplayFilter
End Sub

[HansV]

For example:

Code: Select all

Function DisplayFilter() As String
    Dim i As Long
    Dim ws As Worksheet

    Set ws = ActiveSheet
    If Not ws.FilterMode Then
        DisplayFilter = "No Filter!"
        Exit Function
    End If

    For i = 1 To ws.AutoFilter.Filters.Count
        If ws.AutoFilter.Filters(i).On Then
            DisplayFilter = DisplayFilter & ", " & i
        End If
    Next i
    DisplayFilter = Mid(DisplayFilter, 3)
End Function

Sub ShowFilter()
    MsgBox DisplayFilter, vbInformation
End Sub

[jonnathanjons]

Hello Thank you for the response,

I have removed my previous comment on error which I fixed when googled. this works great. I need a small change .the display currently shows the column number as 3,5,47,102. Can you please change it to the Column name ie Column A ,Column J, Column AV..like that..I tried to change this line to For i = 1 To ws.AutoFilter.Filters.Count To For i = 1 To ws.AutoFilter.Filters.Column.Address its not running. Appreciate your help.
Its my bad that I did not mention it clearly.. thanks again

[HansV]

Will the filtered range always start in column A?

[jonnathanjons]

most of the time.Yes. but if incase not ,Can you pls accommodate that scenario too?

[HansV]

This should do it:

Code: Select all

Function DisplayFilter() As String
    Dim i As Long
    Dim ws As Worksheet
    Dim c As Long

    Set ws = ActiveSheet
    If Not ws.FilterMode Then
        DisplayFilter = "No Filter!"
        Exit Function
    End If

    c = ws.AutoFilter.Range.Column

    For i = 1 To ws.AutoFilter.Filters.Count
        If ws.AutoFilter.Filters(i).On Then
            DisplayFilter = DisplayFilter & ", " & _
                Split(Cells(1, c + i - 1).Address, "$")(1)
        End If
    Next i
    DisplayFilter = Mid(DisplayFilter, 3)
End Function

Sub ShowFilter()
    MsgBox DisplayFilter, vbInformation
End Sub

[jonnathanjons]

Thanks a Lot. This was a 12 years old need fulfilled..cant thanks enough

[HansV]

You're welcome!