application.ontime not cancelling or running in background

Question

I'm using the Application.Ontime command to automatically close a spreadsheet after a period of inactivity (10 minutes).

The following code seems to work in general, however, it appears that if you manually close the sheet yourself, the workbook still seems to be active in the background and at the last designated 'endtime' will open itself so that it can close itself.

This is also evident in the VBA code window as after the CloseWB macro runs and the excel workbook appears to be closed, it is still listed in the VBA project explorer window.

Sub RunTime()
Static EndTime
If Not EndTime = "" Then ActiveWorkbook.Application.OnTime EndTime, "CloseWB", , False
EndTime = Now + TimeValue("00:10:00")
ActiveWorkbook.Application.OnTime EndTime, "CloseWB", , True
End Sub

Sub CloseWB()

    Application.DisplayAlerts = False
    With ThisWorkbook
        .Save
        .Close
    End With
End Sub

I don't want to completely shutdown excel (application.quit) in case users have other workbooks open but need to try and stop the specific workbook running in the background.

Any ideas?

[Related](https://stackoverflow.com/q/27005522/2662901) and [here](https://stackoverflow.com/q/45458979/2662901) and [here](https://stackoverflow.com/q/1674467/2662901) — feetwet, Jan 07 '21 at 17:51

score 1 · Answer 1 · answered May 10 '18 at 15:32

You need to stop the timer. Declare EndTime as a public variable, then turn the timer off in the Workbook_BeforeClose event.

Option Explicit

Public EndTime As Variant

Sub RunTime()
If Not EndTime = "" Then ActiveWorkbook.Application.OnTime EndTime, "CloseWB", , False
EndTime = Now + TimeValue("00:10:00")
ActiveWorkbook.Application.OnTime EndTime, "CloseWB", , True
End Sub

Sub CloseWB()

    Application.DisplayAlerts = False
    With ThisWorkbook
        .Save
        .Close
    End With
End Sub

In the Workbook object:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnTime earliesttime:=EndTime, procedure:="CloseWB", schedule:=False
End Sub

Thanks jblood94. I thought that in a convoluted way I had included that but it seems that simple works best after all! — Bodz38, May 17 '18 at 19:54

application.ontime not cancelling or running in background

1 Answers1

Linked