VBA Status Bar (Hide, Show, and Progress)

HomeVBA TutorialVBA Status Bar (Hide, Show, and Progress)

In VBA, there’s a “Status Bar” property that can help you to show a value to the status bar and there’s also “DisplayStatusBar” to hide and show the status from the Excel window. While using these properties you need to reset the status bar, in the end, otherwise, the last message or setting will stay there.

In the tutorial, we will see a few examples that we can use while working in Excel.

Show a Value on the Status Bar

As I said, you can use the StatusBar property to show a value to the status bar. In the below code, you have used the value “Hello” to add to the status bar.

show a value on the status bar
  1. Use the keyword “Application” to refer to the Excel application.
  2. Type a dot to get the list of properties and methods.
  3. Select the “StatusBar” property.
  4. In the end, use the equals sign to specify the value you want to display on the status bar.
Sub vba_status_bar()
Application.StatusBar = "Hello"
End Sub

Hide the Status Bar using VBA

Now let’s say if you want to hide the status bar you can use the DisplayStatusBar and specify that property to the “False” (consider the following code).

hide the status bar
  1. Use the keyword “Application” to refer to the Excel application.
  2. Type a dot to get the list of properties and methods.
  3. Select the “DisplayStatusBar” property.
  4. In the end, use the equals sign to specify the “False”.
Sub vba_status_bar_hide()
Application.DisplayStatusBar = False
End Sub

Update Progress on Status Bar

The following code runs show a progress counter on the status bar using the count 1 to 100, which you can change as per your need.

Sub vba_status_bar_update()
    
Dim x As Integer
Dim iTimer As Double
    
'you can change the loop if you need
For x = 1 To 100
        
'dummy loop to run, you can change it as well
iTimer = Timer
Do
Loop While Timer - MyTimer < 0.03
        
Application.StatusBar = "Progress: " & x & " of 100: " & Format(x / 100, "Percent")
DoEvents
        
Next x

Application.StatusBar = False
    
End Sub

Important Points to Remember

  1. You need to use the “Application” with the status bar properties to use them.
  2. Once you show a message on the status you need to clear that message.

There’s More

VBA With Statement | VBA Wait and Sleep Commands | VBA ScreenUpdating | VBA Random Number | Line Break in a VBA Code | VBA Immediate Window (Debug.Print) | VBA Concatenate | VBA Module | VBA Random Number