Hi all I have this code to autosave a workbook
Private Sub Workbook_Open()
Application.OnTime Now TimeValue("00:01:00"), "Save1"
End Sub
Sub Save1()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.OnTime Now TimeValue("00:01:00"), "Save1"
End Sub
Problem is now it keeps opening to save. Anyone know what the syntax is to let it save only when its open?
CodePudding user response:
Your problem is that you never stop the timer - it stays active even if you close the workbook. When the minute is over, VBA want to call a Sub (Save1
) that is currently not available (as the workbook is closed), so VBA asks Excel to open the file so that it can execute the routine.
It will not help you if you would somehow add a check if the workbook is open because at that time, it is already open again.
What you need to do is to stop your timer when you close the workbook. The event for that is Workbook_BeforeClose
. Now stopping a timer in VBA is a little bit tricky: You call the Application.OnTime
-method again, and you have to provide the exact parameter that you issued when the timer was set the last time: The name of the routine and the time need to match. The only difference is that you set the 4th parameter (Schedule
) to False.
As a consequence, your code need to keep track what time was provided at the last call to OnTime
. Have a look to the following code:
In the Workbook-Module:
Private Sub Workbook_Open()
StartTimer
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTimer
End Sub
In a regular code module:
Option Explicit
Dim FireTime As Variant ' This holds the time when Ontime was called the last time
Sub StartTimer()
FireTime = Now TimeValue("00:01:00")
Application.OnTime FireTime, "Save1"
' ThisWorkbook.Sheets(1).Cells(1, 1) = FireTime
End Sub
Sub StopTimer()
If Not IsEmpty(FireTime) Then
Application.OnTime FireTime, "Save1", , Schedule:=False
FireTime = Empty
End If
End Sub
Sub Save1()
Debug.Print "tick"
' put your actions here, eg saving
StartTimer ' Schedule the next Timer event
End Sub
CodePudding user response:
You can use:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime Now TimeValue("00:01:00"), "Save1",, False
End Sub