Working With The Excel Statusbar 

 

The status bar portion of the Excel screen gives you a simple and convenient method do display message to the user.  You can use it to display the progress of a VBA procedure, or indicate that the procedure is complete.  The code to put a message on the status bar is shown below.  

Application.StatusBar = "Procedure Complete"

To return control of the status bar back to Excel, set its value to FALSE.  

Application.StatusBar = FALSE

When you use the status bar to display a message to the user, that message will be displayed until your code returns control of the status bar back to Excel.  Excel will not automatically clear it for you.  This may be a problem when you use it to display a "Macro Complete" message -- the message will always remain in the status bar.  

To get around this, you can use an OnTime procedure to clear the status bar after some number of seconds.   For example, consider the code below: 

Sub SomeProcedure() 
'
' your code here
'
Application.StatusBar = "We are done!"
Application.OnTime Now + TimeSerial(0,0,10),"ClearStatusBar"
End Sub

Sub ClearStatusBar()
    Application.StatusBar = FALSE
End Sub

In the SomeProcedure procedure, the last thing we do is tell Excel to run the ClearStatusBar procedure after 10 seconds.  Therefore the status bar will contain the message "We are done!" for 10 seconds, and then it will be cleared, and control of the status bar is returned back to Excel.   

See the Scheduling Procedures page for more information about working with the OnTime method.