how do you display a wait message while

[WANNABE]

a procedure is running behind the scenes??? The only thing I have used is
the msgbox but that pauses the process until you hit a key, and I am looking
to display a message for about 8 seconds, which is how long the process
should take, or until the process completes. How do you do both ??

 

[NickHK]

A MsgBox is always modal; code stops until it has been dismissed.
You can show your own Userform with vbModeless, so code can continue.

NickHK

 

[Guest]

My preference is to use the status bar (where you get the Ready, Calculation,
.... messages) as users are already familiar with this. I then use a message
box at the end to let the user know that everything is done. Something like
this...

sub Whatever()
Application.StatusBar = "Processing. Please Wait..."
'your code here
Application.StatuBar = false
msgbox "Everything completed successfully..."
end sub

 

[Guest]

http://www.j-walk.com/ss/excel/tips/tip34.htm

is one way. show it at the begining of your macro, update it as the macro
progresses, drop it at the end.

 

[WANNABE]

All these responses' were great .. Thank you all!!

 

[elachowitz]

Yet another way... While both the below methods will pause time, only
the Application.Wait command allows for code to continue processing
while time is standing still. Hope this helps.

Application.Wait Now + TimeValue("00:00:03") 'Choose your time

---or---

Option Explicit

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Const sTime = 1000 'Choose your time

Sub yourSub()

....some code, show, hide things...

Sleep sTime

....some code, show, hide things...

End Sub

Enjoy!

 

[Tom Ogilvy]

Think you need to look at the help on Application.Wait. It doesn't let the
code continue to run.

 

[elachowitz]

Tom you are right and I was in haste in my post... I was reading into
pausing time for a progress bar/status message to repaint - I retract
my statement of code continuing when using the application.wait
command... Sorry for any confusion this may have caused.