Hassan Shuvo
Customizing the Excel Status Bar message using VBA allows users to personalize their experience and gain valuable insights during the execution of macros or other tasks. Users may quickly alter the status bar messages based on their unique needs and preferences by employing VBA (Visual Basic for Applications), optimizing productivity, and improving the overall user experience. We will show you how you can display different messages on Status Bar by writing macros on the Visual Basic Editor. Hope you will like this article.
Download Practice Workbook
Download this practice workbook.
What is Status Bar in Excel?
The Status Bar is a horizontal bar located at the bottom of the Excel window. It provides users with important information, updates, and progress indicators about various operations and activities taking place within Excel. The Status Bar is a built-in feature of Excel that serves multiple purposes and offers customization options to enhance the user experience.
The purpose of the Excel Status Bar is to provide users with real-time updates, summary information, and customizable messages. It serves as a progress indicator, displaying percentages and progress bars during ongoing operations. It also offers quick access to statistical measures and results for selected data like sum, average, count, minimum, maximum, etc. To customize the Status Bar in Excel, you can right-click on your mouse while on the Status Bar. This will let you choose what kind of information you want to see on the Status Bar.
Overview of Application.StatusBar Property in Excel VBA
To show custom messages or a progress bar, you can’t use the right-click option. For that, you have to use the VBA code to customize Status Bar. In Excel VBA, Application.StatusBar property is used to display text in the Excel Status Bar. It allows you to provide feedback or display progress information to the user during the execution of a macro or a long-running task. You can also hide or unhide the Status Bar using this property.
By assigning a string value inside a double quotation mark to Application.StatusBar property, you can update the text displayed in the Status Bar. For example, suppose you want to show a message at the completion of a code. Use Application.StatusBar property this way.
Sub updatestatusbar()
Application.StatusBar = “Macro Completed”
End SubThis will show the message Micro Completed on the status bar.
Excel VBA to Show Message on Status Bar: 4 Suitable Cases
In this article, we will see 4 examples of updating the Status Bar using a VBA script. If you are new to Microsoft Visual Basic Application, follow this article on How to Write VBA Code in Excel.
We will see how you can display a unique or static message, a dynamic Status Bar that will display the selected cell content, a progress update message while you are running a VBA macro, and, as a bonus, a progress bar with a percentage of code running. Obviously, we will use the Application.StatusBar property. We will try to give simple explanations for each code so that you can use this property from now on. So, let’s get to the main part of the article.
1. Excel VBA to Show Static Message on Status Bar
To show a static message on Status Bar, you need to set a text/string to the Application.StatusBar property. Here, we want to display our website address in the Status Bar. So let’s understand, step by step, what we need to do.
- First, create a Module in the Visual Basic Editor.
- Then write/paste this code into the Module.
Sub Statusbar_static_message()
Application.StatusBar = “www.exceldemy.com”
End Sub- Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
Code Breakdown
Application.StatusBar = “www.exceldemy.com”, - When you assign a string inside double quotation marks to StatusBar property, it takes that string to display on the Status Bar.
2. Show Cell Content Using VBA as Status Bar Message
Suppose, you want to display your selected cell’s content or value on the Status Bar. This means every time you select a cell, that cell’s content would show up on the Status Bar. So, we need to create an Event that will get triggered every time we select a cell. So, our code should be in the Worksheet_SelectionChange event.
To create an event, you don’t need to create a Module. Follow these steps to better understand.
- First, select and double-click on the worksheet name (e.g., Sheet1, Sheet2) on your Project Explorer in VBA Editor to open the code window for that worksheet.
- In the code window, you will see two drop-down menus at the top: one for the Object and one for the Event. Select the Worksheet object from the left drop-down menu, and SelectionChange event will automatically get selected and a subroutine is created.
- Inside the subroutine write code to display selected cell content. So, the whole code will look like below.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.StatusBar = Target.Value
End Sub- Now if you select any cell, you will get the content of that cell on the Status Bar.
Code Breakdown
Application.StatusBar = Target.Value- The Target parameter inside the event handler subroutine represents the cell that is selected. When you assign Value to the Application.StatusBar property, the code assigns the value of the selected cell to the Excel Status Bar. So, when the SelectionChange event occurs meaning you select any cell, the value of that cell will be shown on the Status Bar.
3. Show Progress Updates on Status Bar Using VBA
While running long code that takes some time to execute completely, you may want to know the progress of that macro. So, it would be great if your Status Bar could show the progress percentage of an active macro. We will create a macro where we will include the Application.StatusBar property to show the completion progress of our code.
To show a progress update message on Status Bar, we created a macro that will generate prime numbers from 1-50. Well, since this is not a long code and its execution time are very low, to show the message properly, we will slow the macro and execute the code every 1 second later. Remember, this is not necessary when you work with your own code. If your code is big and takes time to complete running, your message on the Status Bar will probably be visible regardless.
- First, create a Module in the Visual Basic Editor.
- Then write/paste this code into the Module.
Sub ListPrimeNumbers()
Dim i As Integer, j As Integer
Dim Prime As Boolean
Dim primeCount As Integer
Dim progress As Double
primeCount = 5
For i = 2 To 50
Prime = True
For j = 2 To Sqr(i)
If i Mod j = 0 Then
Prime = False
Exit For
End If
Next j
If Prime Then
Cells(primeCount, "B").Value = primeCount - 4
Cells(primeCount, "C").Value = i
primeCount = primeCount + 1
progress = (i - 2) / 48
Application.StatusBar = "Generating Prime Numbers... " & Format(progress, "0%") & " Completed"
Application.Wait Now + TimeValue("00:00:01")
End If
Next i
Application.StatusBar = "Prime Number Generation Complete"
End Sub
- Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
Code Breakdown
Dim i As Integer, j As Integer
Dim Prime As Boolean
Dim primeCount As Integer
Dim progress As Double- These lines declare the variable names we used in the macro. Here, i and j are loop counters, Prime will indicate a prime number, primeCount tracks the number of prime numbers found. Again, progress stores the progress percentage for display on the status bar.
primeCount = 5- We set primeCount to 5.
For i = 2 To 50
Prime = True- These lines check for prime numbers from 2 to 50 within For Initially, every number is considered prime, which is why it is denoted True.
For j = 2 To Sqr(i)
If i Mod j = 0 Then
Prime = False
Exit For
End If
Next j- Next, we applied another For loop where the value of j was assigned. This checks if the number i is divisible by any number from 2 up to the square root of i. If a divisor is found, Prime is set to False, indicating that the number is not prime. The loop is exited using Exit For as there is no need to continue checking for divisors.
If Prime Then
Cells(primeCount, "B").Value = primeCount - 4
Cells(primeCount, "C").Value = i
primeCount = primeCount + 1- When the code finds the first prime number, that is if Prime is True it starts printing the numbers in cells B5(Serial no) and C5(Prime Number). Then, goes onto find the next number and primeCount is incremented by 1 after each prime number is written.
progress = (i - 2) / 48 - progress calculates the progress percentage based on the current number (i) and the range (48 numbers from 2 to 50).
Application.StatusBar = "Generating Prime Numbers... " & Format(progress, "0%") & " Completed"
Application.Wait Now + TimeValue("00:00:01")
End If
Next i
Application.StatusBar = "Prime Number Generation Complete" - While the code is printing, the StatusBar property shows the progress in the listed format in Status Bar. And when the code finished printing, it printed “Prime Number Generation Complete”.
Note:
Application.Wait Now + TimeValue("00:00:01")This line is unnecessary and in no way related to showing messages on Status Bars. This is used solely for the purpose of delaying the execution of the code so that the message on Status Bar can be visible.
4. Show Progress Bar on Status Bar Using VBA
If you don’t like text-based message on Status Bar, you can also use vertical bars to show progress using VBA in Excel. We will use an updated version of the previous code. In this example, we will show the progress update using vertical bars.
- First, create a Module in the Visual Basic Editor.
- Then write/paste this code into the Module.
Sub ListPrime()
Dim i As Integer, j As Integer
Dim Prime As Boolean
Dim primeCount As Integer
Dim progress As Double
Dim progressBar As String
primeCount = 5
progressBar = ""
For i = 2 To 50
Prime = True
For j = 2 To Sqr(i)
If i Mod j = 0 Then
Prime = False
Exit For
End If
Next j
If Prime Then
Cells(primeCount, "B").Value = primeCount - 4
Cells(primeCount, "C").Value = i
primeCount = primeCount + 1
progress = (i - 2) / 48
progressBar = "[" & String(progress * 48, "|") & String((1 - progress) * 48, " ") & "]"
Application.StatusBar = progressBar & Format(progress, "0%") & " Completed"
Application.Wait Now + TimeValue("00:00:01")
End If
Next i
Application.StatusBar = "[" & String(48, "|") & "]" & "Generation Completed"
End Sub
- Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
Code Breakdown
Dim i As Integer, j As Integer
Dim Prime As Boolean
Dim primeCount As Integer
Dim progress As Double
Dim progressBar As String- These lines declare the variable names we used in the macro. Here, i and j are loop counters, Prime is a boolean indicating a prime number, primeCount tracks the number of prime numbers found. Again, progress stores the progress percentage. Lastly, progressBar stores a string in the form of vertical bars(“|”) on the status bar.
primeCount = 5
progressBar = ""- We set primeCount to 5 and progressBar to be an empty string.
For i = 2 To 50
Prime = True- These lines check for prime numbers from 2 to 50 within For Initially, every number is considered prime, so it is denoted True.
For j = 2 To Sqr(i)
If i Mod j = 0 Then
Prime = False
Exit For
End If
Next j- Next, we applied another For loop where the value of j was assigned. This checks if the number i is divisible by any number from 2 up to the square root of i. If the code finds a divisor, it will set Prime to False, indicating that the number is not prime. We used Exit For to exit the loop as there is no need to continue checking for divisors.
If Prime Then
Cells(primeCount, "B").Value = primeCount - 4
Cells(primeCount, "C").Value = i
primeCount = primeCount + 1- When the code finds the first prime number, that is if Prime is True it starts printing the numbers in cells B5(Serial no) and C5(Prime Number). Then, goes onto find the next number and primeCount is incremented by 1 after each prime number is written.
progress = (i - 2) / 48
progressBar = "[" & String(progress * 48, "|") & String((1 - progress) * 48, " ") & "]"- progress calculates the progress percentage based on the current number (i) and the range (48 numbers from 2 to 50). progressBar generates a string representation of the progress bar using the “|” character for completed progress and space for remaining progress.
Application.StatusBar = progressBar & Format(progress, "0%") & " Completed"
Application.Wait Now + TimeValue("00:00:01")
End If
Next i- While the code is printing, the StatusBar property shows the progress bar in the listed format in Status Bar.
Application.StatusBar = "[" & String(48, "|") & "]" & "Generation Completed"- And when the code finished printing, it printed “[||||||||||||||||||||||||||||||||||||||||||||||||] Generation Complete”. We used 48 bars to print as the 100% completion of the code. Use as many as you want. Just change the formula in the progress and progressBar variables while you choose anything different.
Note:
Application.Wait Now + TimeValue("00:00:01")This line is unnecessary and in no way related to showing messages on Status Bars. We used this is solely for the purpose of delaying the execution of the code so that it can make the message on Status Bar visible.
How to Hide Excel Status Bar Using VBA
While the Status Bar is very handy in its uses if you are one of those users who don’t want to see this tool at the bottom of the Excel workbook, you can hide it using VBA Script. We will use the Application.DisplayStatusBar property to hide the Status Bar.
- First, create a Module in the Visual Basic Editor.
- Then write/paste this code into the Module.
Sub hide_static_bar()
Application.DisplayStatusBar = False
End Sub- Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
Code Breakdown
DisplayStatusBar = False- By assigning False, we basically just ask Excel not to show the Status Bar. Remember that any changes you might have done to the Status Bar will still be there. By writing this line you are just hiding it.
How to Unhide Excel Status Bar Using VBA
If you for any reason have hidden your Excel Status Bar and now want to unhide it again, you can do that by assigning True to the Application.DisplayStatusBar property.
- First, create a Module in the Visual Basic Editor.
- Then write/paste this code into the Module.
Sub Unhide_static_bar()
Application.DisplayStatusBar = True
End Sub- Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
Code Breakdown
DisplayStatusBar = True- By assigning True, we basically just ask Excel to display the Status Bar.
How to Clear Excel Status Bar Message using VBA
Suppose you have put a message on the Excel Status Bar, and you want it to be gone or cleared after a certain period of time. To clear any message from the Status Bar you have to put False as the value of Application.StatusBar property.
To understand the concept of clearing the message on Excel Status Bar, we will use an extra code that will display a custom message (“www.exceldemy.com”) for a certain period (5 Second), and the code will then clear the Status bar.
- First, create a Module in the Visual Basic Editor.
- Then write/paste this code into the Module.
Sub clear_message()
Application.StatusBar = "www.exceldemy.com"
Application.OnTime Now + TimeValue("00:00:05"), "clearStatusBar"
End Sub
Sub clearStatusBar()
Application.StatusBar = False
End Sub
- Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
Code Breakdown
This includes two VBA subroutines: “clear_message” and “clearStatusBar.”
Sub clear_message()
Application.StatusBar = "www.exceldemy.com"
Application.OnTime Now + TimeValue("00:00:05"), "clearStatusBar"
End Sub- This subroutine, clear_message, is responsible for displaying (“exceldemy.com”) on the Status Bar for 5 seconds and then calling the clearStatusBar subroutine to clear the Status Bar. It uses the OnTime method of the Application object to specify the time and to execute the name of the subroutine to.
Sub clearStatusBar()
Application.StatusBar = False
End Sub- This subroutine sets the StatusBar property to False, effectively removing any message displayed on it.
Frequently Asked Questions
1. What is the purpose of displaying messages on the Excel Status Bar?
Ans: Users can receive real-time feedback regarding the status of activities or operations they are being carrying out in Excel by having messages shown on the Status Bar. By keeping users updated on ongoing procedures, it enhances user experience.
2. Can I use the Excel Status Bar message for error handling or displaying error messages?
Ans: While you can use the Status Bar for general task feedback, we do not recommend you it for error handling or displaying detailed error messages. It’s more appropriate to use message boxes or other dialog boxes to communicate errors to users effectively.
3. Can I customize the appearance of the Excel Status bar?
Ans: No, the application has predefined appearance of the Excel Status Bar and you cannot customize it through VBA.
Conclusion
By updating the Status Bar with relevant information, such as progress updates, notifications, or other feedback, you can keep users informed about ongoing operations without disrupting their workflow. With VBA‘s versatility and ease of use, you may tailor the Status Bar messages to your individual requirements, resulting in a more dynamic and user-friendly Excel program. We hope, our 4 examples can give you the necessary ideas as to updating Excel Status Bar message using VBA and benefit you as such.
