Auto Save macro keeps running after workbook is closed

[MurrayBarn]

Hi There

I have a short macro in a workbook (not in Personal.xlsb) that saves said workbook every ten minutes as I am using some functions that prevents Excel autosave running for some reason. It works really well but the problem is if I close said workbook, the macro keeps running. It opens the workbook and saves it every ten minutes even if closed. The only way to stop this is close Excel completely and then open up the other workbooks. It is quite annoying as I often have lots of workbooks open. Below is the code I am using. My question is is there some VBA code that will remove the below code from whatever memory Excel keeps when this particular workbook is closed?

Sub SaveThis()
   
     Application.DisplayAlerts = False
     ThisWorkbook.Save
     Application.DisplayAlerts = True
    
     Application.OnTime Now + TimeValue("00:10:00"), "SaveThis"
End Sub

 

[John_w]

You can fix the problem by cancelling the pending OnTime event when you close the workbook. To do that, the code must provide the exact time that the OnTime procedure is scheduled to run, and therefore we must store the time in a Public variable, which is then used to schedule or cancel the OnTime event. Your code needs a bit of restructuring, along with additional code.

Code in a standard module:

Option Explicit

Public RunWhen As Double

Sub StartTimer()
    RunWhen = Now + TimeValue("00:10:00")
    Application.OnTime RunWhen, "SaveThis", , True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime RunWhen, "SaveThis", , False
End Sub

Sub SaveThis()
     Application.DisplayAlerts = False
     ThisWorkbook.Save
     Application.DisplayAlerts = True
     StartTimer
End Sub

Code in the ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
    StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

 

[MurrayBarn]

You can fix the problem by cancelling the pending OnTime event when you close the workbook. To do that, the code must provide the exact time that the OnTime procedure is scheduled to run, and therefore we must store the time in a Public variable, which is then used to schedule or cancel the OnTime event. Your code needs a bit of restructuring, along with additional code.

Code in a standard module:

Option Explicit

Public RunWhen As Double

Sub StartTimer()
    RunWhen = Now + TimeValue("00:10:00")
    Application.OnTime RunWhen, "SaveThis", , True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime RunWhen, "SaveThis", , False
End Sub

Sub SaveThis()
     Application.DisplayAlerts = False
     ThisWorkbook.Save
     Application.DisplayAlerts = True
     StartTimer
End Sub

Code in the ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
    StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

Thank you, I'll give it a go