Hi, everyone.
I have a workbook with a scheduled event using application.ontime to close after 30 minutes of inactivity (activity defined as selecting a different cell in the worksheet). There is a bug in it somewhere that is causing the workbook to open up again later. I'm pretty confident that my syntax for unscheduling the event is correct, so I was wondering if there is a way to view all scheduled events within application.ontime. Can this be done? Seeing what is scheduled will help me troubleshoot.
Just so you know how I'm working, here is the code I'm using below.
Code
Private Sub Workbook_Open()
Call ResetTimer 'Call the module to start a timer from this moment (used to auto close the workbook on inactivity)
End SubCode
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call ResetTimer 'Call the module to start a timer from this moment (used to auto close the workbook on inactivity)
End SubCode
Option Explicit
Public ScheduledTime As Date
Public Sub ResetTimer()
On Error Resume Next 'Ignore any errors
Application.OnTime EarliestTime:=ScheduledTime, Procedure:="AutoClose", Schedule:=False 'Unschedule any previously scheduled countdowns
ScheduledTime = Time + TimeValue("00:30:00") 'Update the current time
Application.OnTime ScheduledTime, "AutoClose" 'Run the TimeOut module once the set time in the future has been reached
On Error GoTo 0 'Revert error handling to default
End Sub
Public Sub AutoClose()
ThisWorkbook.Close savechanges:=True 'Close the workbook after the specified time out period
End SubCode
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlManual 'Turn off automatic calculation
Application.ScreenUpdating = False 'Turn off screen updating
Application.EnableEvents = False 'Turn off events
Application.DisplayAlerts = False 'Turn off alerts
'Unschedule the auto-timeout event
On Error Resume Next 'Ignore any errors
Application.OnTime EarliestTime:=ScheduledTime, Procedure:="AutoClose", Schedule:=False 'Unschedule any previously scheduled countdowns
On Error GoTo 0 'Revert error handling to default
Application.DisplayAlerts = True 'Turn on alerts
Application.EnableEvents = True 'Turn on events
Application.ScreenUpdating = True 'Turn on screen updating
Application.Calculation = xlAutomatic 'Turn on automatic calculation
End Sub