Shared Workbook - Auto close when idle too long, still showing as open?

[Alan_P]

Hi All,

I'm wondering if someone can help, I have a shared workbook that I've added the below code to so that it automatically saves and closes if someone has it open for 30 minutes and hasn't used it (it was constantly annoying people leaving it open all the time!!)

It works perfectly except for one thing... When I go to un-share the workbook to do updates it still says several people have it open even tho I know this isn't the case.

Does anyone know what causes this and how to solve it?

Much appreciated,
Cheers,
Alan.

In ThisWorkbook:

Private Sub Workbook_Open()
a = MsgBox("Please close this file when not being used!" & vbNewLine & "File will be closed after 30 minutes of inactivity", vbCritical, "********** WARNING **********")
nElapsed = TimeSerial(0, 30, 0) '30 minutes
nTime = Now + nElapsed
Application.OnTime nTime, "Countdown"
End Sub

-------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime EarliestTime:=nTime, Procedure:="Countdown", Schedule:=False
End Sub

-------------------------------------------------------------
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnTime nTime, "Countdown", , False
nTime = Now + nElapsed
Application.OnTime nTime, "Countdown"
End Sub

-------------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime nTime, "Countdown", , False
nTime = Now + nElapsed
Application.OnTime nTime, "Countdown"
End Sub

-------------------------------------------------------------
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime nTime, "Countdown", , False
nTime = Now + nElapsed
Application.OnTime nTime, "Countdown"
End Sub

In a standard Module:

Option Explicit
Public nElapsed As Double
Public nTime As Double

-------------------------------------------------------------
Public Sub Countdown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub