Hello group,

If a "MsgBox" is open, it prevents "Application.OnTime" from executing the
procedure.

I want to emulate this behavior. In other words: I'm looking for a way to
delay the OnTime event until a button is pushed.

Does anybody know how "MsgBox" does this, or how it can me accomplished
otherwise?

Thanks.

Emile van Mierlo

Look at Help for OnTime. OnTime will fire only if XL is in Ready, Copy,
Cut or Find mode. If another macro is running (i.e., if a sub is
displaying a MsgBox), then OnTime will wait until XL is in one of those
modes, or until LatestTime, whichever is earlier.



In article ,
"Emile van Mierlo" wrote:

Hello group,

If a "MsgBox" is open, it prevents "Application.OnTime" from executing the
procedure.

I want to emulate this behavior. In other words: I'm looking for a way to
delay the OnTime event until a button is pushed.

Does anybody know how "MsgBox" does this, or how it can me accomplished
otherwise?

If a "MsgBox" is open, it prevents "Application.OnTime" from executing
the
procedure.
I want to emulate this behavior. In other words: I'm looking for a way
to
delay the OnTime event until a button is pushed.
Does anybody know how "MsgBox" does this, or how it can me accomplished
otherwise?

Look at Help for OnTime. OnTime will fire only if XL is in Ready, Copy,
Cut or Find mode. If another macro is running (i.e., if a sub is
displaying a MsgBox), then OnTime will wait until XL is in one of those
modes, or until LatestTime, whichever is earlier.
Ok, but how do I get Excel in a mode that holds OnTime? (How does MsgBox do
that)

Thanks,

Emile

MsgBox does that by being displayed by a macro - the running macro takes
XL out of Ready mode.

In article ,
"Emile van Mierlo" wrote:

Ok, but how do I get Excel in a mode that holds OnTime? (How does MsgBox do
that)

Ok, but how do I get Excel in a mode that holds OnTime? (How does MsgBox
do
that)

MsgBox does that by being displayed by a macro - the running macro takes
XL out of Ready mode.


If I run a macro which opens a form --similar to a MsgBox-- it will not
prevent the OnTime event from executing; so something more happens in a
MsgBox.

I'm looking for the mechanism that MsgBox uses to change the mode. Just the
fact that MsgBox is called from a macro is not it.

Thanks.

Emile

It does appear that the fact that the macro has started but not finished
is not enough to stop OnTime firing.

If I run test below and leave the userform on the screen, the timer
ticks away till it's finished.

However, the MsgBox does not appear until I dismiss the userform.

Sub test()
timed 0
UserForm1.Show vbModal
MsgBox "dunnit"
End Sub

Sub timed(Optional x% = -1)
Static counts%
If x% < -1 Then counts = x%
counts% = counts% + 1
Application.StatusBar = "Counts=" & counts
If counts < 10 Then
Application.OnTime Now + TimeValue("00:00:01"), "timed"
Else
Application.StatusBar = ""
End If
End Sub


Emile van Mierlo wrote:
Ok, but how do I get Excel in a mode that holds OnTime? (How does MsgBox

do

that)


MsgBox does that by being displayed by a macro - the running macro takes
XL out of Ready mode.



If I run a macro which opens a form --similar to a MsgBox-- it will not
prevent the OnTime event from executing; so something more happens in a
MsgBox.

I'm looking for the mechanism that MsgBox uses to change the mode. Just the
fact that MsgBox is called from a macro is not it.

Thanks.

Emile

I think you're making an unwarranted assumption that a UserForm is
"similar to a MsgBox", at least in WinXL. There's nothing inherently
special about MsgBox - it works just like the Wait method or the
InputBox method - it halts the macro until the user presses a button,
but doesn't change XL's operating mode.

In MacXL, a userform acts exactly like a msgbox - code halts until the
userform is dismissed, including OnTime macros. WinXL's userforms, even
modal forms, don't act the same way.




In article ,
"Emile van Mierlo" wrote:

If I run a macro which opens a form --similar to a MsgBox-- it will not
prevent the OnTime event from executing; so something more happens in a
MsgBox.

I'm looking for the mechanism that MsgBox uses to change the mode. Just the
fact that MsgBox is called from a macro is not it.

If I run test below and leave the userform on the screen, the timer
ticks away till it's finished.

However, the MsgBox does not appear until I dismiss the userform.

Exactly. The code stops, but the timer keeps running.
When using a MsgBox, the code stops _and_ the timer stops.

Now, how can we stop the timer, like MsgBox can.

Emile

I think you're making an unwarranted assumption that a UserForm is
"similar to a MsgBox", at least in WinXL. There's nothing inherently
special about MsgBox - it works just like the Wait method or the
InputBox method - it halts the macro until the user presses a button,
but doesn't change XL's operating mode.

Ok, lets change the question: Do you know _how_ to stop the timer?

Thanks.

Emile

Do you mean pause the timer or stop it?

Either way, you can either kill it or kill it and reset it.

Take a look at Chip Pearson's site:
http://www.cpearson.com/excel/ontime.htm
particularly: Stopping A Timer Process



Emile van Mierlo wrote:

I think you're making an unwarranted assumption that a UserForm is
"similar to a MsgBox", at least in WinXL. There's nothing inherently
special about MsgBox - it works just like the Wait method or the
InputBox method - it halts the macro until the user presses a button,
but doesn't change XL's operating mode.

Ok, lets change the question: Do you know _how_ to stop the timer?

Thanks.

Emile

--

Dave Peterson

Do you mean pause the timer or stop it?

No, I don´t want to stop the timer, or pause the timer, I want to pause the
execution of the macro when the time is up until --for example-- a button is
pushed.

I will give an example to clarify myself:

When the Userform1 is activated (modal) , the timer is armed to close itself
after 10 seconds.
Userform1 has 2 buttons. CommandButton1 activates a MsgBox, and this one
prevents the timer from firing until the MsgBox is dismissed.
CommandButton2 opens another form, but does not prevent the timer from
firing, and result in an error message because it tries to unload the modal
form with a childform still open.

'These procedures are located on userform1
Private Sub UserForm_Activate()
'This Arms the timer to unload itself
Application.OnTime Now + TimeValue("00:00:05"), "closeThisForm"
End Sub

Private Sub CommandButton1_Click()
MsgBox "This will prevent the timer from firing until the OK button is
pushed"
End Sub

Private Sub CommandButton2_Click()
'This will not prevent the timer from firing,
'and result in an error message because the timer
'tries to unload the the modal form.with a childform still open
UserForm2.Show
End Sub


'This procedure to unload the form is located in a module
Private Sub closeThisForm()
Unload UserForm1
End Sub


What I am looking for, is a way to prevent the timer in Userform1 form
firing while child UserForm2 is still open.
So what code should I add to Userform2 so it prevents the timer on userForm1
from firing until this form is dismissed like MsgBox does.

Thanks.

Emile

Perhaps:

Regular Code Module:

Public dFireTIme As Double


UserForm1:

Private Sub UserForm_Activate()
dFireTime = Now + TimeSerial(0, 0, 5)
Application.OnTime dFireTime, "closeThisForm"
End Sub

Private Sub CommandButton2_Click()
Application.OnTime dFireTime, "closeThisForm", Schedule:=False
UserForm2.Show
End Sub

UserForm2:

Private Sub UserForm_Deactivate()
Application.OnTime Now + TimeSerial(0, 0, 1), "closeThisForm"
End Sub



In article ,
"Emile van Mierlo" wrote:

What I am looking for, is a way to prevent the timer in Userform1 form
firing while child UserForm2 is still open.
So what code should I add to Userform2 so it prevents the timer on userForm1
from firing until this form is dismissed like MsgBox does.

This way UserForm1 never closes if CommandButton2 is clicked.
And in case of the MsgBox, Userform1 closes after the MsgBox is dismissed.
In other words, MsgBox keeps the timer from firing until it is dismissed.

Thanks.

Emile

Perhaps:

Regular Code Module:

Public dFireTIme As Double


UserForm1:

Private Sub UserForm_Activate()
dFireTime = Now + TimeSerial(0, 0, 5)
Application.OnTime dFireTime, "closeThisForm"
End Sub

Private Sub CommandButton2_Click()
Application.OnTime dFireTime, "closeThisForm", Schedule:=False
UserForm2.Show
End Sub

UserForm2:

Private Sub UserForm_Deactivate()
Application.OnTime Now + TimeSerial(0, 0, 1), "closeThisForm"
End Sub