Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Canceling a Macro that Has Been Scheduled
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Canceling a Macro that Has Been Scheduled
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
canceling simplifying my fractions | New Users to Excel | |||
Run Excel macro from Scheduled Task | Excel Worksheet Functions | |||
Self canceling checkboxes | Excel Discussion (Misc queries) | |||
Create macro to copy data on a scheduled basis | Excel Discussion (Misc queries) | |||
canceling auto date update | New Users to Excel |