Cancel Ontime routine

[Riddler]

I have a ontime routine that gets called when the workbook is opened.
It has the spreadsheet recalculate every 5 seconds. That works fine.
The problem I have is trying to cancel it. I use the exact same syntax
when I started it with the exception of Schedule:= False to cancel it.
I get a "Method 'OnTime' of Object'_Application' failed" error. Can
anyone help figure out what I am missing or is wrong?

Thanks
Scott

Sub AutoUpDater()
Calculate 'Recalculates the spreadsheet
Application.OnTime EarliestTime:=Now + TimeValue("00:00:05"), _
procedure:="AutoUpDater", Schedule:=True
End Sub

Sub CancelAutoUpdater()
Application.OnTime EarliestTime:=Now + TimeValue("00:00:05"), _
procedure:="AutoUpDater", Schedule:=False
End Sub

 

[Chip Pearson]

To clear an OnTime routine, you must provide the EXACT time for which the
event was scheduled. Clearly (or it should be clear) that you can't simply
use "Now+TimeValue("00:00:05")" as the time of the event because Now will
return a different value (the current time of day) every time it is called.
Store your time to run in a Public variable (declared above and outside of
any procedure) and use that value.

Public RunWhen As Double
Sub AutoUpdater()
RunWhen = Now+TimeSerial(0,0,5)
Application.OnTime RunWhen,"AutoUpdater",,True
End Sub

Sub CancelOnTime()
Application.OnTime RunWhen,"AutoUpdater",,False
End Sub

See www.cpearson.com/Excel/OnTime.aspx for additional details.


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

 

[Dave Peterson]

Take a look at Chip Pearson's notes:
http://www.cpearson.com/excel/OnTime.aspx

Pay particular attention to how he stores the next time (in the RunWhen
variable) so he can cancel any pending ontime procedure.

 

[Riddler]

Works great! I was wondering how "exact" exact meant. I thought the 5
second interval was all that it needed but obviously it didnt work.
Thank a bunch.

Scott Riddle
Mech. Eng.