Workbook with Timer Macro Saving After Closing

[vettaforza]

I have a workbook that has a timer macro in it that will save and close the book after 2 minutes of inactivity. That works fine. When you have another instance of excel open, and you close the workbook that has the timer macro in it, the workbook with the timer macro will still save, you can see it pop up briefly on the other excel sheet you have open. I'm not sure what to do.

Option Explicit
Dim ResetTime As Date

Sub StartTimer()
    ResetTime = Now + TimeValue("00:00:30") ' Set initial timer for 2 minutes
    Application.OnTime ResetTime, "CloseWorkbook" ' Schedule closing the workbook at the specified time
End Sub

Sub ResetTimer()
    On Error Resume Next
    Application.OnTime ResetTime, "CloseWorkbook", , False ' Cancel previously scheduled close event
    ResetTime = Now + TimeValue("00:00:30") ' Reset timer for another 2 minutes
    Application.OnTime ResetTime, "CloseWorkbook" ' Schedule closing the workbook again
End Sub

Sub Workbook_Open()
    StartTimer ' Start the timer when the workbook is opened
End Sub

Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ResetTimer ' Reset the timer when any activity is detected
End Sub

Public Sub StartTimer()
    If Not TimerStarted Then
        Application.OnTime Now + TimeValue("00:00:30"), "CloseWorkbook"
            TimerStarted = True
        End If
End Sub
Public Sub CloseWorkbook()
    If Now > LastActivity + TimeValue("00:00:30") Then
    On Error Resume Next
    If Not ThisWorkbook.ReadOnly Then
        ThisWorkbook.Save
    End If
    If Not ThisWorkbook.ReadOnly Then
        ThisWorkbook.Close
    End If
SaveChanges = False
    Else
        StartTimer
    End If
End Sub

 

[John_w]

When you have another instance of excel open, and you close the workbook that has the timer macro in it, the workbook with the timer macro will still save, you can see it pop up briefly on the other excel sheet you have open.

You need to cancel the timer when the workbook is closed.

In the ThisWorkbook module:

Sub Workbook_Close()
    On Error Resume Next
    Application.OnTime ResetTime, "CloseWorkbook", , False ' Cancel previously scheduled close event
End Sub