Cancel Ontime routine

R

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
 
C

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)
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run time error 2
Error using OnTime Method 3
Cancelling Application.OnTime - error message 2
Cancelling Application.OnTime 4
Simple Variable to someone 5
Cancel OnTime 1
Stopping a timer 10
making a countdown timer 1

Top