help needed with msgbox

[Marcus Bischoff \(AnW/P-AB9\)]

Hi NG.

I have a litte problem with my Macro. I would like to show a message-box
with vbyesno-buttons. This msgbox should stay open for about 5-10sec and
then close automatically.
In the msgbox I ask the user if he wants to open an Excel-file, and he can
answer "yes" or "no", as expected
But at the msgbox the macro stops and waits until the user clicks a button.
Now I don't want the macro to wait for eternity and go on after the
specified time. If the user didn't click a button in this time the
msgbox-result should be set to "yes" and the box should close.
I think an application.wait is not the right way, because excel waits before
the msg is shown or the msg appears before the wait command is active.
Or do I have to create a UserForm?

Has anyone an idea how to solve this?

Thanks in advance.

MB

 

[Bob Phillips]

Hi MB,

Here is an example of a timed msgbox.

Dim cTime As Long
Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")
cTime = 10 ' 10 secs
Select Case WSH.Popup("Open an Excel file?!", cTime, "Question",
vbOKCancel)
Case vbOK
MsgBox "You clicked OK"
Case vbCancel
MsgBox "You clicked Cancel"
Case -1
MsgBox "Timed out"
Case Else
End Select


As you can see, it can be OK, Cancel or timed out.


--

HTH

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

 

[Marcus Bischoff \(AnW/P-AB9\)]

works fine. Thanks a lot.

 

[Fred]

Marcus,

As you rigthly point out, it won't work with a Messagebox. The code stops
running until a button is pressed. Also, using Application.Wait or Do While
loops with timers dont work as they wait for the time to pass, before giving
control back.
So what I would do is the following:

1. Make a custom Userform with a "Yes" and "No" button.
2. In the UserForm_Activate put the following code: Call OnTimeStart
3. In the code for the Yes and No buttons, put the following code, before
the code you want to run when Yes or No is pressed: Call OnTimeStop
4. In a module, declare a Public Variable ExecTime: Public ExecTime As Date
[Don't forget ;-)]
5. In the module put the following code:

Sub OnTimeStart()

ExecTime = Now + TimeSerial(0, 0, 10) ' 10 is your delay in seconds
Application.OnTime ExecTime, "TestA", , True
MsgBox ("Time pressed: " & Format(Now, "hh:mm:ss") & " - Scheduled: " &
Format(ExecTime, "hh:mm:ss"))

End Sub

Sub OnTimeStop()

Application.OnTime ExecTime, "TestA", , False
MsgBox ("Stopped at :" & Format(Now, "hh:mm:ss") & " - Scheduled: " &
Format(ExecTime, "hh:mm:ss"))

End Sub

Sub TestA()

MsgBox ("Ontime Prcedure Called after elapsed time")
'Put your code to open the file
'Also add code to unload the userform (Unload "UserFormName")

End Sub

So what happens:
- When your Yes-No-UserForm is activated it calls the "OnTimeStart" sub,
which schedules the "TestA" procedure to start 10 seconds after Now
- If the user does not press the yes or no button within 10 seconds, the
TestA procedure (for example to open the file) is started and the userform
is unloaded
- If the user does press a button, the "OnTimeStop" procedure is called,
which cancels the previously set schedule to run "TestA" after 10 seconds
and runs whatever code you put in the commandbutton_click

Hope this works for you.

Good Luck !