Macro 61: Show Filtered Columns in the Status Bar
When you have a large table with many columns that are AutoFiltered, it is sometimes hard to tell
which columns are filtered and which aren’t. Of course, you could scroll through the columns, peer-
ing at each AutoFilter drop-down list for the telltale icon indicating the column is filtered, but that
can get old quickly.
This macro helps by specifically listing all the columns that are filtered in the status bar. The status
bar is the bar (seen here in Figure 5-7) that runs across the bottom of the Excel window.
Status bar
Figure 5-7: This macro lists all filtered columns in the status bar.
How it works
This macro loops through the fields in our AutoFiltered data set. As we loop, we check to see if each
field is actually filtered. If so, we capture the field name in a text string. After looping through all the
fields, we pass the final string to the StatusBar property.
Sub Macro61()
‘Step 1: Declare your Variables
Dim AF As AutoFilter
Dim TargetField As String
Dim strOutput As String
Dim i As Integer
‘Step 2: Check if AutoFilter exists – If not Exit
If ActiveSheet.AutoFilterMode = False Then
Application.StatusBar = False
Exit Sub
End If
‘Step 3: Set AutoFilter and start looping
Set AF = ActiveSheet.AutoFilter
For i = 1 To AF.Filters.Count
‘Step 4: Capture filtered field names
If AF.Filters(i).On Then
TargetField = AF.Range.Cells(1, i).Value
strOutput = strOutput & ” | ” & TargetField
End If
Next
‘Step 5: Display the filters if there are any
If strOutput = “” Then
Application.StatusBar = False
Else
Application.StatusBar = “DATA IS FILTERED ON ” & strOutput
End If
End Sub
1. Step 1 declares four variables. AF is an AutoFilter variable that is used to manipulate the
AutoFilter object. TargetField is a string variable we use to hold the field names of any
field that is actually filtered. strOutput is the string variable we use to build out the final
text that goes into the status bar. Finally, the i variable serves as a simple counter, allowing
us to iterate through the fields in our AutoFilter.
2. Step 2 checks the AutoFilterMode property to see if sheet even has AutoFilters applied.
If not, we set the StatusBar property to False. This has the effect of clearing the status
bar, releasing control back to Excel. We then exit the procedure.
3. Step 3 sets the AF variable to the AutoFilter on the active sheet. We then set our counter to
count from 1 to the maximum number of columns in the AutoFiltered range. The AutoFilter
object keeps track of its columns with index numbers. Column 1 is index 1; column 2 is index
2, and so on. The idea is that we can loop through each column in the AutoFilter by using the
i variable as the index number.
4. Step 4 checks the status of AF.Filters object for each (i) – i being the index number
of the column we are evaluating. If the AutoFilter for that column is filtered in any way, the
status for that column is On.
If the filter for the column is indeed on, we capture the name of the field in the TargetField
variable. We actually get the name of the field by referencing the Range of our AF AutoFilter
object. With this range, we can use the Cells item to pinpoint the field name. Cells(1,1)
captures the value in row one, column one. Cells(1,2) captures the value in row one,
column two, and so on.
As you can see in Step 4, we have hard-coded the row to 1 and used the i variable to indi-
cate the column index. This means that as the macro iterates through the columns, it always
captures the value in row one as the TargetField name (row one is where the field name
is likely to be).
After we have the TargetField name, we can pass that information a simple string con-
tainer (strOutput in our case). strOutput simply keeps all the target field names we find
and concatenates them into a readable text string.
5. Step 5 first checks to make sure that there is something in the strOutput string. If
strOutput is empty, it means the macro found no columns in our AutoFilter that were
filtered. In this case, Step 5 simply sets the StatusBar property to False, releasing
control back to Excel.
If strOutput is not empty, Step 5 sets the StatusBar property to equal some helper text
along with our strOutput string.
How to use it
You ideally want this macro to run each time a field is filtered. However, Excel does not have an
OnAutoFilter event. The closest thing to that is the Worksheet_Calculate event. That being
said, AutoFilters in themselves don’t actually calculate anything, so you need to enter a “volatile”
function on the sheet that contains your AutoFiltered data. A volatile function is one that forces a
recalculation when any change is made on the worksheet.
In the sample files that come with this book, notice that we use the =Now() function. The Now func-
tion is a volatile function that returns a date and time. With this on the sheet, the worksheet is sure to
recalculate each time the AutoFilter is changed.
Place the Now function anywhere on your sheet (by typing =Now() in any cell). Then copy and paste
the macro into the Worksheet_Calculate event code window:
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. In the Project window, find your project/workbook name and click the plus sign next to it in
order to see all the sheets.
3. Click on the sheet from which you want to trigger the code.
4. Select the Calculate event from the Event drop-down list (see Figure 5-8).
5. Type or paste the code.
Figure 5-8: Type or paste your code in the Worksheet_Calculate event code window.
In order to make the code run as smoothly as possible, consider adding these two pieces of code
under the worksheet calculate event:
Private Sub Worksheet_Deactivate()
Application.StatusBar = False
End Sub
Private Sub Worksheet_Activate()
Call Worksheet_Calculate
End Sub
Also, add this piece of code in the workbook BeforeClose event:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = False
End Sub
The Worksheet_Deactivate event clears the status bar when you move to another sheet or
workbook. This avoids confusion as you move between sheets.
The Worksheet_Activate event fires the macro in Worksheet_Calculate. This brings back
the Status Bar indicators when you navigate back to the filtered sheet.
The Workbook_BeforeClose event clears the status bar when you close the workbook. This
avoids confusion as you move between workbooks.
