Solved: Auto Close WorkBook when Idle

[jammer6_9]

I have a Workbook which is Save in the NetWork. It allows only 1 user at a time to open by

Private Sub Workbook_Open()
If ThisWorkbook.ReadOnly Then
ThisWorkbook.Saved = True
ThisWorkbook.Close SaveChanges:=False
End If
End Sub

My problem now is most of the time user forget to close the workbook then leave the office which made the file in use for the whole day. Is there any solution to AutoClose the WorkBook when it is Idle or can I set a user to to use the file for 15 minutes only.

[Bob Phillips]

Option Explicit

'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
nElapsed = TimeSerial(0, 5, 0) '5 minutes
'start a timer to countdown inactivity
nTime = Now + nElapsed
Application.OnTime nTime, "Shutdown"
End Sub

'-----------------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'-----------------------------------------------------------------
'cancel outstanding timer
Application.OnTime nTime, "Shutdown", , False
'any workbook activity resets the timer
nTime = Now + nElapsed
Application.OnTime nTime, "Shutdown"
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code



Put this code in a standard code module



Option Explicit

Public nElapsed As Double
Public nTime As Double

'-----------------------------------------------------------------
Sub Shutdown()
'-----------------------------------------------------------------

ThisWorkbook.Save
ThisWorkbook.Close

End Sub

[jammer6_9]

Thanks xld for the response. I havent try your code but as I have checked the following thread

http://vbaexpress.com/forum/showthread.php?t=11531

It answers my question and works fine....