Nov 4 2008

Using the Status Bar to Display Messages

Excel provides an easy way to show the end user messages about the status of a macro that is running. This can be a useful way of updating users, especially during long running macros, as to the status of the routine. Some users need updates from the system to let them know that the program isn’t frozen, it’s just running (and so the don’t close it down unnecessarily).

Here’s an image of a message being displayed on the status bar.



Excel Status Bar


Looking at the code, it’s easy to see how this can be used within your own projects.


Sub UpdateStatusBar()
‘Updates the status bar with our message, then resets
‘  it to its previous state
‘Saves status of StatusBar = if currently shown = true
CurrentStatusBarShown = Application.DisplayStatusBar

‘Show the Status Bar
Application.DisplayStatusBar = True

‘Display the message you want
Application.StatusBar = “Here’s my status bar message!”

‘Remove the message from the status bar
Application.StatusBar = False

‘Resets status of StatusBar to previous state
Application.DisplayStatusBar = CurrentStatusBarShown

End Sub

Remember that using a single quote is the way the macro knows to ignore that line and sees that as a commented section.  Reading through the comments, you can see this is a simple macro that:

  1. Saves the current status of the status bar.
  2. Turns the Status bar on so it is displayed on the screen.
  3. Set the StatusBar message to our custom message to be displayed.
  4. Clears the StatusBar message.
  5. Returns the Status bar back to its original state.

The best way to see this in action is to walk through the code line by line, while watching the status bar at the bottom of the screen as it changes. To walk-through the code, make sure the cursor is inside the subroutine, and hit F8 on your keyboard to go line by line.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 


Comments are closed.

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks