Cancelling Application.OnTime

[Dan]

Hi,
I need to run a process repeatively, but I need to be able to stop it too or
change the timing , but keep getting the following error when trying to stop
the process " Run-Time error '1004': Method 'ONTIME' of Object 'Application'
Failed"

Any idea why, or how I should do it instead.
Many thanks


Sub RunMacro()
Application.OnTime Now + TimeValue("00:00:05"), "OnTimeMacro"

End Sub

Sub OntimeMacro()
MsgBox "hello"
RunMacro

End Sub

Sub byebye()
Application.OnTime EarliestTime:=TimeValue("00:00:05"),
Procedure:="my_Procedure", Schedule:=False ' this is where I keep getting the
error?
MsgBox "Bye Bye"
End Sub

 

[Leith Ross]

Hi,
I need to run a process repeatively, but I need to be able to stop it too or
change the timing , but keep getting the following error when trying to stop
the process " Run-Time error '1004': Method 'ONTIME' of Object 'Application'
Failed"

Any idea why, or how I should do it instead.
Many thanks

Sub RunMacro()
Application.OnTime Now + TimeValue("00:00:05"), "OnTimeMacro"

End Sub

Sub OntimeMacro()
MsgBox "hello"
RunMacro

End Sub

Sub byebye()
Application.OnTime EarliestTime:=TimeValue("00:00:05"),
Procedure:="my_Procedure", Schedule:=False ' this is where I keep getting the
error?
MsgBox "Bye Bye"
End Sub

Hello Dan,

This should help. It is from the VBA help file...

OnTime Method Example

This example runs my_Procedure 15 seconds from now.

Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"
This example runs my_Procedure at 5 P.M.

Application.OnTime TimeValue("17:00:00"), "my_Procedure"
This example cancels the OnTime setting from the previous example.

Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
Procedure:="my_Procedure", Schedule:=False

Sincerely,
Leith Ross

 

[Dan]

Hi,
Thanks, but that is exactly my question - I have access to this page too,
and I copied it but as per my question this is not working and this was what
I was asking.

 

[Chip Pearson]

You need to pass the EXACT time of the timer back to OnTime in order to
cancel the timer. Therefore, you should create a public variable outside of
any procedure and store the time in that variable.

Public RunWhen As Double

Sub RunMacro()
RunWhen = Now + TimeValue("00:00:05")
Application.RunWhen, "OnTimeMacro"
End Sub

Sub byebye()
Application.OnTime EarliestTime:=RunWhen, _
Procedure:="my_Procedure", Schedule:=False
MsgBox "Bye Bye"
End Sub

See http://www.cpearson.com/excel/ontime.aspx for full details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Dan]

Thank you very much

You need to pass the EXACT time of the timer back to OnTime in order to
cancel the timer. Therefore, you should create a public variable outside of
any procedure and store the time in that variable.

Public RunWhen As Double

Sub RunMacro()
RunWhen = Now + TimeValue("00:00:05")
Application.RunWhen, "OnTimeMacro"
End Sub

Sub byebye()
Application.OnTime EarliestTime:=RunWhen, _
Procedure:="my_Procedure", Schedule:=False
MsgBox "Bye Bye"
End Sub

See http://www.cpearson.com/excel/ontime.aspx for full details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)