When a range of cells are selected, Excel automatically displays formula results on the status bar. This can be an incredibly useful feature of Excel, especially when combined with filtering data.
Excel can display the sum, average, max, min, count of cells containing values and count of cells that are not blank in the status bar.
This blog post looks at how you can get Excel to display your own function on the status bar. For example, you may need to know the median or mode value, or count the number of blanks in the selected range.
In this post we will get Excel to display the number of blank cells in the selected range on the status bar.
Adding Your Own Function to the Status Bar
To add our own function to the status bar, we need to write a macro that performs the function and displays the answer on the status bar. We then need to use the Worksheet Selection event to run this macro when a selection is made.
Creating the Macro to Store a Function on the Status Bar
- Open the Visual Basic Editor by pressing Alt + F11
- Insert a module into the active workbook by clicking the Insert menu and selecting Module.
- Copy and paste the code below into the module.
This code declares 2 public variables. One to store the count of the number of blanks, and the other to store the range of the selected cells.
The COUNTBLANK function is used to count the blanks and store it in the variable. The status bar is then activated and the result displayed.
Public NoBlank as long Public rng As Range Sub NumberofBlanks() NoBlank = Application.WorksheetFunction.CountBlank(rng) Application.DisplayStatusBar = True Application.StatusBar = "Blanks: " & NoBlank End Sub
Running the Code when a Range is Selected
- From the Visual Basic Editor, double click the worksheet on the project explorer that you want to run the code from.
- Select Worksheet from the Object list and then SelectionChange from the Procedure list.
- Copy and paste the code into the event procedure as shown below.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set rng = Target Call NumberofBlanks End Sub
Whenever a range of cells are selected from that sheet, the macro is run and counts the number of blanks (Learn more awesome Excel VBA techniques).