Excel vba end autosave ontime loop

Question

The following code autosaves based on a cell value.

Sub AutoSaveIt()
If Sheets("BOH General").Range("A102").Value > 0 Then
    ThisWorkbook.Save
    Application.OnTime Now + TimeSerial(0, Sheets("BOH General").Range("A102").Value, 0), "AutoSaveIt"
End If
End Sub

What code do I call at close to break this loop?

EDIT: I'm trying to avoid it re-opening the workbook and running the macro again once it has been closed

Related question, perhaps: http://stackoverflow.com/q/4599796/2127508 — barrowc, Jul 03 '15 at 02:43

score 1 · Accepted Answer · edited May 08 '18 at 21:49

barrowc's link gave me the search term I needed, to an article here

I came up with some code that seems to be working - an abbreviated version here if anyone needs it:

Public runwhen As Variant


Sub AutoSaveTimer()

If Sheets("BOH General").Range("A102").Value > 0 Then
    RunWhen = Now + TimeSerial(0, Sheets("BOH General").Range("A102").Value, 0)
    Application.OnTime EarliestTime:=RunWhen, Procedure:="AutoSaveIt", _
        Schedule:=True
Else
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:="AutoSaveIt", _
        Schedule:=False
    On Error GoTo 0
End If

End Sub


Sub AutoSaveIt()

ThisWorkbook.Save
Call AutoSaveTimer

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Cancel autosave
Sheets("BOH General").Range("A102").Value = 0
call AutoSaveTimer

End Sub

Gilligan · Answer 2 · 2015-07-03T01:00:48.797

0

Could you use a BeforeClose event to change your A102 value to zero?

Maybe pair it with a Workbook_Open event to change it back.

EDIT:

    Sub AutoSaveIt()
        If ThisWorkbook.Sheets(1).Range("A1").Value > 0 Then
            ThisWorkbook.Save
            Application.OnTime Now + TimeSerial(0, ThisWorkbook.Sheets(1).Range("A1").Value, 0), "AutoSaveIt"
            Debug.Print "saved at " & Now()
        End If
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        ThisWorkbook.Sheets(1).Range("A1").Value = 0
        ThisWorkbook.Save
        Application.Quit
    End Sub


    Private Sub Workbook_Open()
         ThisWorkbook.Sheets(1).Range("A1").Value = 1
         Application.OnTime Now + TimeSerial(0, 0, ThisWorkbook.Sheets(1).Range("A1").Value), "AutoSaveIt"
    End Sub

edited Jul 03 '15 at 01:00

answered Jul 03 '15 at 00:05

Gilligan

176
3
9

Done that, it still opens the workbook again, runs the macro, finds that the value is 0 and doesn't autosave, I'm trying to avoid it re-opening the workbook – matt9292 Jul 03 '15 at 00:08
What if you use Application.Quit at the end of your BeforeClose event? – Gilligan Jul 03 '15 at 00:53
Considered that but users often have more than one sheet open at the same time – matt9292 Jul 03 '15 at 01:09

Excel vba end autosave ontime loop

2 Answers2