PDA

View Full Version : Solved: Message while running macros



sb003848
08-05-2009, 08:37 AM
Hello,

I'm currently running a macro in Excel that take a few minutes to do (goes through multiple worksheets and also gets info from 3 different files.

The macro runs great but since it's taking a little bit of time to complete, I would like to have a message show up saying to "Please Wait" while the macro is running and another message "All done" once the macro is completed...

Thanks in advance for anyone able to help me out...

mdmackillop
08-05-2009, 09:02 AM
You can show a message in the Status Bar. From the Help file
StatusBar Property Example
This example sets the status bar text to "Please be patient..." before it opens the workbook Large.xls, and then it restores the default text.
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Please be patient..."
Workbooks.Open Filename:="LARGE.XLS"
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar

Dadmin
08-05-2009, 02:53 PM
Mr. mdmackillop,

In my situation, I set the worksheet visibility to false to speed up processing and get input from a userform. Does the userform have a status bar?

Application.Visible = False

GTO
08-05-2009, 04:16 PM
Greetings Dadmin,

The status bar is at the bottom of Excel's window, not in a userform. If you have the userform displayed (or want to make another userform to display) during calculations/runtime while the macro runs, you might want to take a look at this vbaexpress KB entry:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=169

Mark

Dadmin
08-05-2009, 04:58 PM
Thanks, GTO. That looks like it'll work for me. I'm just bummed that VBA doesn't allow MsgBox to be one-way with a Null feedback. In the good old days of DOS, you could simulate hitting the Enter key and redirect it to a command line in a batch file.

macropod
08-05-2009, 07:38 PM
See also: http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/