CODEWITHSUNDEEP
excelvbaontime
Bodz38
Bodz38

Reputation: 21

application.ontime not cancelling or running in background

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?

Upvotes: 2

Views: 1461

Answers (1)

jblood94
jblood94

Reputation: 17001

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

Upvotes: 1

Related Questions