CODEWITHSUNDEEP
excelvba
tech
tech

Reputation: 19

condition to save workbook only when open

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?

Upvotes: 1

Views: 65

Answers (2)

FunThomas
FunThomas

Reputation: 29181

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

Upvotes: 2

Aldert
Aldert

Reputation: 4313

You can use:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime Now + TimeValue("00:01:00"), "Save1",, False
End Sub

Upvotes: 0

Related Questions