Cancelling a Previously Scheduled Macro

It is fairly difficult to cancel a previously scheduled macro. You must know the exact time that the macro is scheduled to run. To cancel a pending operation, call the OnTime method again, using the Schedule:=False parameter to unschedule the event. The following code cancels the 11:00 a.m. run of CaptureData:

Sub CancelEleven()

Application.OnTime EarliestTime:=TimeValue("11:00 AM"), _

Procedure:=CaptureData, Schedule:=False End Sub

It is interesting to note that the .OnTime schedules are remembered by a running instance of Excel. If you keep Excel open but close the workbook with the scheduled procedure, it still runs. Consider this hypothetical series of events:

2. Open Schedule.XLS and run a macro to schedule a procedure at 8:00 a.m.

3. Close Schedule.xls but keep Excel open.

4. Open a new workbook and begin entering data.

At 8:00 a.m., Excel re-opens Schedule.xls and runs the scheduled macro. Excel doesn't close Schedule.xls. As you can imagine, this is fairly annoying and alarming if you are not expecting it. If you are going to make extensive use of Application.Ontime, you might want to have it running in one instance of Excel while you work in a second instance of Excel.

0 0

Post a comment