Canceling a Macro that Has Been Scheduled

  • Thread starter Thread starter Murray Williams
  • Start date Start date
M

Murray Williams

I have a macro that runs every 15 minutes in a spreadsheet
to update some data. I'm using code similar to the
following to schedule the macro:

1. Application.OnTime Now + TimeValue
("00:00:15"), "my_Procedure"

If I close my spreadsheet, but keep excel open, when the
time has come for the macro to run again, the macro
attempts to run again by opening up the file that I had
closed. What is the best way to stop this?

The help files describe a way to cancel a macro if it has
been scheduled for a specific time:

2. Application.OnTime TimeValue("17:00:00"), "my_Procedure"

3. Application.OnTime EarliestTime:=TimeValue("17:00:00"),
_
Procedure:="my_Procedure", Schedule:=False

I haven't been able to figure out how to cancel it if
example 1 was used to schedule the macro. I tried using
variations of #3 in the before_close event for the
workbook. Is there a way to determine which macros are
scheduled to run and what time they have been scheduled to
run?

thanks,

Murray Williams
 
Murray,

To cancel an OnTime macro, you must specify the *exact* time the
macro is scheduled for. Therefore, instead of code like

Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

You should store the time in a global variable, and use that with
OnTime. E.g.,

Dim RunWhen As Double
Sub StartTimer()
RunWhen = Now + TimeValue("00:00:15")
Application.OnTime RunWhen, "my_Procedure"
End Sub

Then, you can use the RunWhen to cancel the procedure:

Application.OnTime RunWhen,,,False

See www.cpearson.com/excel/ontime.htm for more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Chip,

Thanks a lot ... it works!

Murray
-----Original Message-----
Murray,

To cancel an OnTime macro, you must specify the *exact* time the
macro is scheduled for. Therefore, instead of code like

Application.OnTime Now + TimeValue ("00:00:15"), "my_Procedure"

You should store the time in a global variable, and use that with
OnTime. E.g.,

Dim RunWhen As Double
Sub StartTimer()
RunWhen = Now + TimeValue("00:00:15")
Application.OnTime RunWhen, "my_Procedure"
End Sub

Then, you can use the RunWhen to cancel the procedure:

Application.OnTime RunWhen,,,False

See www.cpearson.com/excel/ontime.htm for more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)






.
Click to expand...
 
Back
Top