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"), _
procedu="AutoUpDater", Schedule:=True
End Sub

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

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)

"Riddler" wrote in message
ps.com...
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"), _
procedu="AutoUpDater", Schedule:=True
End Sub

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

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.

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 wrote:

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"), _
procedu="AutoUpDater", Schedule:=True
End Sub

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

--

Dave Peterson