Selected Answer
Hi FREX_420
The application Timer operates on an Earliest Time basis and so can be delayed if you do something in Excel.
Your file also resets the 15 second timer when there's an entry made of new sheet picked (since the Workbook_SheetCalculate or Workbook_SheetSelectionChange event macros stop and reset that timer).
If you need a countdown timer and want the file to close after 15 seconds (or as close as possible to that, given activity), please try the revised file attached.
At a workbook level, it just sets cell S2 and starts the seconds countdown timer using this:
Private Sub Workbook_Open()
Call SetTimer
Call Timer
End Sub
with the event macros disabled (commented out).
On your modules, my chnages are (with comments):
Option Explicit
Public DownTime As Date
Sub SetTimer()
' Just set the time and allow Timer sub do the work
Range("S2").Value = "00:00:15"
DownTime = Now + TimeValue("00:00:15")
' ### lose this line
' Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", Schedule:=True
End Sub
' ### lose this sub
'Sub StopTimer()
' On Error Resume Next
' Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", Schedule:=False
'End Sub
Sub ShutDown()
Application.DisplayAlerts = False
ThisWorkbook.Close SaveChanges:=True
End Sub
and the other module:
Option Explicit
Dim Interval As Date
Sub Timer()
' set timer value (one second)
Interval = Now + TimeValue("00:00:01")
If Worksheets("Sheet1").Range("S2").Value <= 0 Then
' reached 0 with no running timer (so just need to quit)
Call ShutDown
Else
' if not 0, decrement S2
Worksheets("Sheet1").Range("S2") = DownTime - Now
Application.OnTime EarliestTime:=Interval, Procedure:="Timer"
End If
End Sub
Sub Stop_Timer()
Application.OnTime EarliestTime:=Interval, Procedure:="Timer", Schedule:=False
End Sub
If you don't need the countdown, you could instead use just the 15 second timer and its Stop in a similar way- that would probably lead to an earlier closure since as soon as an activity stops, the expired timer would lead to the file being closed.
Hope this fixes things for you. If so, please rember to mark this Answer as Selected.
