Canceling Application.ontime loops

[aaron.irvine]

Hey guys,

I didn't realise that when you set an Application.ontime loop it does NOT stop simply by closing the document. I've got some funky stuff going on as a result, with spreadsheets popping up all over the place and generating backups when they haven't been active.

So how can I stop these already-looping macros from triggering, and ensuring I never code this way again?!

[apo]

Here's an example of a Timer being Started and also Stopped..

In the example below. the Sub StopTimer is setting the Schedule to False.
You could call that from a button or put it in the Workbook Close event..

Please Login or Register  to view this content.

[Izandol]

To stop the current scheduled macros, simply close the whole application and restart it.

In the example posted by apo there is a missing declaration for dTime which must be module-level or Public and not within a routine.

[john55]

see this http://www.cpearson.com/excel/OnTime.aspx

[apo]

Izandol is right..

Put this at the top of the Module for my example..

Please Login or Register  to view this content.

[aaron.irvine]

Thanks for the feedback guys, much appreciated.

Izandol, I'm finding that when x & y workbooks are open, z workbook (which in this case is an old unused workbook that had an Application.ontime command set without code to stop the time) is opening itself randomly as the timers are still active.

I wouldn't know how many files I've set a number of timers to activate, without realising I needed to also code them to stop! I guess one solution is to just delete the offending files, but should I need to keep them, is there a way to generically kill all timers that were previously set (or is it just a matter of modding the code and implementing the StopTimer() function?)

Cheers!

[Izandol]

Closing the application clears any timers. You may then open the workbooks with macros disabled and modify the code as required.

[aaron.irvine]

Thank you! I guess that the PCs that have been having this issue have always had Excel open with one spreadsheet or another - therefore the application had never been closed.