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 procedu

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



"Murray Williams" wrote in
message
...
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"),
_
Procedu="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



.