Using the Status Bar to Display Messages
- 1 Comment
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.
[ad#in-post-ad]
Looking at the code, it’s easy to see how this can be used within your own projects.
Example
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 = CurrentStatusBarShownEnd 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:
- Saves the current status of the status bar.
- Turns the Status bar on so it is displayed on the screen.
- Set the StatusBar message to our custom message to be displayed.
- Clears the StatusBar message.
- 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
1 Comments on this post
Trackbacks
-
Amit said:
Can we show message on status bar without VBA
November 8th, 2008 at 2:09 pm