Canceling a Macro that Has Been Scheduled

[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

 

[Chip Pearson]

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)

 

[Murray Williams]

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)






.