Stopping Timed Macro on Workbook Close

[Si-Phy]

I have a workbook that runs a timed macro.

However whilst I have other Workbooks open this Macro continues to run.

How to you stop any macros on Workbook?

I have tried this but cant get it to work :-

Please Login or Register  to view this content.

[Norie]

That's only going to stop the time macro when you close the workbook it's in.

When exactly don't you want the timed macro to run?

[BellyGas]

This bit:

Please Login or Register  to view this content.

Sets the kick off time to the time now + 50 minutes. ie, if you run this at 1pm, the kick off time will be set to 1:50pm.

To turn it off you need to specify the exact time it was scheduled, ie 1:50pm. You're using the same command, ie whatever time it is closed + 50 minutes. When you kick the timer off you need to record the time it's going to run somewhere so you can use the same time when you turn it off.

[Si-Phy]

Thank you for your quick responses

The macro is to run at 50 min intervals from opening.

But the close of the workbook is the point at which this should stop. It could be at anytime within the day.

[BellyGas]

Again, you can only stop it if you specify the time it is supposed to run.

Imagine it's a train. The 13:50 from Waterloo.

If you put in a cancel notification you're not cancelling the 14:50 from waterloo, you're cancelling the 13:50 from waterloo.....and you need to tell Excel that it's the 13:50 from waterloo you're cancelling, not any other trains from waterloo that might arrive at a different time.


ie. When you first run the application.ontime and tell Excel that at Now + 50 mins (eg 13:50) run the macro called xxxxxx. You MUST record the now + 50 mins time somewhere so that you can use the EXACT SAME TIME when you remove the scheduling on exit.

geddit now?

[Norie]

Try something like this.

Please Login or Register  to view this content.

[Si-Phy]

Thanks Norie

I have tried that one, unfortunately the workbook_before close element errors.

Runtime error 1004

Method 'OnTime' of object '_Application' failed

[Norie]

Try declaring tmExit as Public in a standard module (Insert>Module).

Please Login or Register  to view this content.

[Si-Phy]

Unfortunately the same error. Im running an autosave ( which I believe is generally frowned upon )

As noted before its just that if I have another excel document open but close the one running the "SaveThis" it keeps trying to run although the parent workbook is closed

[Logit]

.
Post all of your Timer code for review please.

[Si-Phy]

In 'ThisWorkbook'

Please Login or Register  to view this content.

In 'Module1' :-

Please Login or Register  to view this content.

In 'Module2'

Please Login or Register  to view this content.

In 'Module3'

Please Login or Register  to view this content.

[xlnitwit]

Hi,

Did you manually run the Workbook_Open code after you made the changes? If not, you'd get an error the first time you try to close the workbook, as the time variable wasn't set.

[Si-Phy]

Hi,

Thank you, not sure what I was doing wrong but it would seem to be OK now.

Only issue is that the code was to run every 50 minutes from the time of opening. The code only seems to run this once ?

Please Login or Register  to view this content.

[BellyGas]

Well it would do. The application ontime isn't a repeating scheduler. It schedules one run of the macro specified at the specified time.

In order to run it again add the same code to the macro that runs, logging the new scheduled run time so you can turn it off on close.

[Si-Phy]

Thank you ,

As I am a bit of a novice , it is only this bit ( being that the other parts are turning things on and off essentially ) ?

Please Login or Register  to view this content.

[xlnitwit]

No, you need to add the OnTime as well

Please Login or Register  to view this content.

to the end of the SaveThis routine.

[Si-Phy]

Thank you all , managed to sort that with a bit of trial and erro. Obviously using the full bit of code.

Please Login or Register  to view this content.

Added to the Macro as noted