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