Stopping Timed Macro on Workbook Close

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 :-

 Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Application.OnTime Now + TimeValue("00:50:00"), Procedure:="SaveThis", schedule:=False
 End Sub


 Private Sub Workbook_Open()
 Application.OnTime Now + TimeValue("00:50:00"), Procedure:="SaveThis", schedule:=True
 End Sub

This bit:

Application.OnTime Now + TimeValue("00:50:00"), Procedure:="SaveThis", schedule:=True

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.

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.

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?

Thanks Norie

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

Runtime error 1004

Method 'OnTime' of object '_Application' failed

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

.
Post all of your Timer code for review please.

In 'ThisWorkbook'

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime tmExit, Procedure:="SaveThis", schedule:=False
     
End Sub


Private Sub Workbook_Open()
    tmExit = Now + TimeValue("00:50:00")
    Application.OnTime tmExit, Procedure:="SaveThis", schedule:=True
End Sub

In 'Module1' :-

Sub SaveThis()
Application.DisplayAlerts = True
Call RefreshSummary
Application.DisplayAlerts = True
End Sub

In 'Module2'

Public tmExit As Variant

In 'Module3'

Sub RefreshSummary()
Application.ScreenUpdating = False
Call PopulateList
Call MakeOneColumn
Call MakeOneColumn1
Call dedup
Call selectinfo
Call selectinfo1
ThisWorkbook.Save
Sheets("SUMMARY").Visible = False
Sheets("Single List").Visible = False
Sheets("Tasks and Times").Visible = False
Sheets("DATA").Visible = False
Sheets("GRAPHS").Select
Application.ScreenUpdating = True
End Sub

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 ?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime tmExit, Procedure:="SaveThis", schedule:=False
End Sub


Private Sub Workbook_Open()
    tmExit = Now + TimeValue("00:50:00")
    Application.OnTime tmExit, Procedure:="SaveThis", schedule:=True
End Sub

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.

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 ) ?

tmExit = Now + TimeValue("00:50:00")

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

tmExit = Now + TimeValue("00:50:00")
    Application.OnTime tmExit, Procedure:="SaveThis", schedule:=True

Added to the Macro as noted