Close Workbook when Sheet is inactive

[eli_m]

Hi,

I currently have this regular Module:

Dim DownTime As Date

Sub SetTimer()
    DownTime = Now + TimeValue("00:30:00") ' This sets the timer for 30 minutes of inactivity
    Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", Schedule:=False
 End Sub

Sub ShutDown()
        MsgBox "Timed out after 30 minutes - Your work has been saved and closed"
    With ThisWorkbook
        Application.DisplayAlerts = False
        .Close Savechanges:=True
        Application.DisplayAlerts = True
    End With
End Sub

And I have these in My Sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Call StopTimer 'Stop Timeout timer
    Call SetTimer 'Set Timeout timer

If Application.CutCopyMode = False Then
Application.Calculate ' Refresh for Grey Line
End If
End Sub

Private Sub Worksheet_Calculate()

    Call StopTimer 'Stop Timeout timer
    Call SetTimer 'Set Timeout timer

' Ignore Errors after Sorting
Dim r As Range: Set r = Range("A2:AW200")
Dim cel As Range

For Each cel In r
  With cel
    .Errors(8).Ignore = True 'Data Validation Error
    .Errors(9).Ignore = True 'Inconsistent Error
    .Errors(6).Ignore = True 'Lock Error
  End With
Next cel
End Sub

All is working fine and the Workbook closes after 30 minutes.
I want to make it when I am on another sheet that it doesn't close.

I tried stopping the time with the below code but with no success:

Private Sub Worksheet_Activate()
   Call StopTimer 'Stop Timeout timer
  ThisWorkbook.RefreshAll ' Auto Refresh Pivot Tables
 
On Error Resume Next

End Sub

Any help would be great!

Thanks in advance

 

[eli_m]

Anyone able to help with this ?

Thanks in advance,

 

[Candyman8019]

It looks like if you try to stop the timer before it is running it will error out.

Try changing the stoptimer to this. This way, if the timer is already stopped for some reason it will ignore the error and continue.

Sub StopTimer()
    On Error Resume Next
    Application.OnTime DownTime, "ShutDown", , False
 End Sub

 

[eli_m]

It looks like if you try to stop the timer before it is running it will error out.

Try changing the stoptimer to this. This way, if the timer is already stopped for some reason it will ignore the error and continue.

Sub StopTimer()
    On Error Resume Next
    Application.OnTime DownTime, "ShutDown", , False
 End Sub

amazing! thank you - this is what worked