Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Ontime routine
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Ontime routine
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Ontime routine
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Ontime routine
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find %ontime & SUMIF ontime ie: find matching sets within Range... | Excel Worksheet Functions | |||
Cancel Button Routine | Excel Programming | |||
Cancel OnTime | Excel Programming | |||
Cancel Macro is user selects 'cancel' at save menu | Excel Programming | |||
how do I make a routine run after the 'cancel' butten is pressed . | Excel Programming |