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).
Is it possible to create an addin that accomplishes this?
I don’t see why not. Procedures that we write can be saved as Add-Ins and distributed.
Could you please show me how to show the median of selected cells? I don’t know how to write the macro or how to trade it out for the macro you gave as an example.
Hi Marv, Excel has a Median worksheet function so you can just replace this statement
Application.WorksheetFunction.CountBlank(rng)
With this
Application.WorksheetFunction.Median(rng)
Assign it to a variable and adapt the text in the Status Bar as you please.
hi
i also need to show the median of a range; I have absolutely no VBA knowledge;
can you please write the whole code I should type ?
many thanks
I added the Median function. How do I get it to display decimals instead of just whole numbers? Also, every time I click on a non-valued cell, I get Run-time error message 1004 and unable to find the median. How do I get rid of this window that keeps popping up
Hi Spencer,
The below code will sort it. This uses a double variable type to show decimals and an error trap to handle the error you received.
Public MedianValue As Double
Public rng As Range
Sub FindMedian()
On Error GoTo DefaultMedian
MedianValue = Application.WorksheetFunction.Median(rng)
Application.DisplayStatusBar = True
Application.StatusBar = “Median Value: ” & MedianValue
Exit Sub
DefaultMedian:
Application.StatusBar = “Median Value: No Median Found”
End Sub
For the selectionchange event I have this.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set rng = Target
Call FindMedian
End Sub