Inactivity timer re-opens workbook

[gmoney2996]

Hello,

First time poster so I apologize if I break any sort of rules.

I have a workbook which has a timer that will kick out the user if the worksheet doesn't change for 30 minutes.

The timer works fine if I have no other instances of excel open. But, as soon as I have another workbook open and I try to close the workbook with the timer, the workbook closes but then reopens right away.

I've found a few ideas that would suggest I'm missing something in the "Workbook_BeforeClose" section, I just cannot figure out what. Any help would be much appreciated. Thanks!

In the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Private Sub Workbook_Open()
MsgBox ("This Workbook has a 30 minute inactivity timer.")
Call StartTimer
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call ResetCount
End Sub

In module:

Option Explicit
Public StopTime As Date
Public gCount As Long
Public Const kLIMIT = 1800  '***30 MINUTE LIMIT***

Sub Timer()
Dim tTime
    tTime = Now + TimeValue("00:00:01")
    Application.OnTime tTime, "ClickTimer"
End Sub

Sub ClickTimer()
Dim tTime
Dim time_remaining As Integer

tTime = tTime - TimeSerial(0, 0, 1)
gCount = gCount + 1
ThisWorkbook.Worksheets("PTE INPUT").Range("S6").Value = Format(((kLIMIT - gCount) / 60), "00.00")
If gCount > kLIMIT Then
    'MsgBox "Countdown complete."
    Application.StatusBar = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close True
    Exit Sub
End If
Call Timer
End Sub

Public Sub ResetCount()
gCount = 0
StopTime = Now + TimeValue("00:00:01")
End Sub

Public Sub StartTimer()
Call ResetCount
Call ClickTimer
End Sub

 

[John_w]

Untested and I haven't looked closely at your code.

In the module, delete this line in Sub Timer:

Dim tTime

Add this line above all procedures:

Public tTime As Date

Add this:

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

Add this to the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub