message box while macro is running

[wendy]

is it possible to have message box pop up when an
auto_open marco calls another macro? Basically, i will
like it to tell the user that the application is running.
and disappears once the marco stops.

example of steps.
1.user click on application
2.application auto_open and run marco
3.message box appear with message "marco is in process"
option for user to click OK(the macro in process is based
on the processing time of the marco that is called by
sub auto_open())
4.second message box "marco has completed" option for
user to click OK (the macro in completed is based on the
completion time of the marco that is called by sub
auto_open())

I have completed step one and two. i need help with the
message box, step 3 and 4
please let me know if i have to create a form
Thanks in advance

 

[Colo]

Hi wendy,

The macro will not start until [OK] button is clicked in the message
box.
So I think using an userform is better in your case.
Please have a look @ attached sample file.

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=401942

 

[Guest]

SO it is not possible to auto run the macro and receive a
prompt saying the marco is running?

 

[Colo]

Sorry, as far as I know, there is no way using a message box while th
macro is running.
So I showed a sample with using an Userform.

Please wait reply from others

 

[Bob Phillips]

Wendy,

Don 't know how you can get it to be even t drfiven, but I do know how to
display a MsgBox that will automatically time out after n seconds. This is
that code

Dim nRes As Long
Dim oWSH As Object

Set oWSH = CreateObject("WScript.Shell")
nRes = oWSH.Popup(Text:="Click a button, or just wait ", _
SecondsToWait:=2, _
title:="Automatic MsgBox Timeout", _
Type:=vbYesNoCancel)


The msgbox will timeout after 2 seconds. If the user clicks a button on the
box, it returns vbYes or vbNo, whereas if the msgbox times out, it
returns -1.

I suppose you could set the time to some inordinate length and use SendKeys
to force a Yes or No, but it is getting a bit messy.

I person ally would use a Userform.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Here is John Walkenbach's page on creating a Splash Screen:
http://j-walk.com/ss/excel/tips/tip39.htm

It uses a userform such as Colo suggests (didn't look at his file, so don't
know if the approaches are identical).

A msgbox is modal - so all activity halts until you respond to it. You
could put this up and say, click the button to continue, but the code is
halted until the user hits the button.

Jim Rech put up some code that calls a timed msgbox using one of the windows
scripting languages, but many people posted back that it didn't close for
them.

http://groups.google.com/groups?threadm=#ghpFrjPCHA.2048@tkmsftngp08

Sub SelfClosingMsgBox()
Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")
wsh.Popup "Hello!", 2, "This closes itself in 2 seconds"
End Sub


--
Regards,
Tom Ogilvy

SO it is not possible to auto run the macro and receive a
prompt saying the marco is running?

-----Original Message-----
Hi wendy,

The macro will not start until [OK] button is clicked in the message
box.
So I think using an userform is better in your case.
Please have a look @ attached sample file.

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=401942

 

[wendy]

Thanks Everyone. Colo has solved my problem since the
first posting, it was my fault. Since i have to marco
with one calling the the other, i need to add the
beginning statement on both. Thank you very very much COLO

 

[Colo]

Hi wendy, glad I could help you.
You are welcome.

 

[JenReyn]

Wendy,

I don't know how to make messages stay on the screen while
a macro processes. But sometimes if I have a macro that
may take a while to process, I put a msgbox at the
beginning that says something like "Application is running
and will take about 3 minutes to finish. Click ok to
continue."

Good luck,
JenReyn

 

[RHerb]

Wendy:
I have a little macro that will display a userform for a set time. Th
userform name is "Patience" and it has text as well as a picture.

It works well EXCEPT the timer won't start with the MODAL=True, whic
you need to bring up the picture with the userform. With MODAL=False
timer starts and it runs perfectly. To get the timer to start wit
MODAL=True, you need to click label1 on the Userform. Notice I have
clock/timer that displays in "B7".

Someone can surely tell us how to start the timer with MODAL=True.

Simple code:

*****
Sub Time_Macro()

Patience.Show
S = Second(Time()) + 10
While Second(Time()) < S
Range("B7").Value = Time()
Wend
Patience.Hide

End Sub

Sub Show()
Patience.Show
End Sub

Sub ShowEnd()
'The following screen updating may not be needed. It works eithe
way.
Application.ScreenUpdating = True
Patience.Hide
End Sub


And, once in a while it won't end, but I think it is the "Wend" tha
needs to be changed