MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Time out and exit

  Asked By: Madeleine    Date: Nov 14    Category: MS Office    Views: 913

Is there a way to have a workbook automatically close if not used for a
certain length of time?



2 Answers Found

Answer #1    Answered By: Hoor Khan     Answered On: Nov 14

We do this all the time  with Access databases
(http://support.microsoft.com/?id=128814). I have adapted the same concept for

In the ThisWorkbook module of the workbook, paste the following code:

Private Sub Workbook_Open()
'Set StartTime when the workbook  is opened.
StartTime = Timer
'Schedule a call to CheckTime in the future to check elapsed idle time.
Application.OnTime (Now + TimeValue(TimeCheckDelay)), "CheckTime"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Something changed in the workbook, so reset StartTime.
StartTime = Timer
End Sub

In A VBA code module in the same workbook, paste this code:

Global StartTime As Single
Global Const TimeLimitInMinutes = 58 'idle time threshold
Global Const TimeCheckDelay = "00:10:00"

Sub CheckTime()
Dim NewTime As Single
'Get the time (seconds past midnight) now.
NewTime = Timer
'If StartTime was yesterday, add 86400 seconds to NewTime.
If NewTime < StartTime Then
NewTime = NewTime + 86400
End If
'If TimeLimitInMinutes has expired since StartTime was last
'updated, close  the workbook without saving changes.
If (NewTime - StartTime) > (TimeLimitInMinutes * 60) Then
ThisWorkbook.Saved = True
ThisWorkbook.Close SaveChanges:=False
'Otherwise, schedule a call to CheckTime in the future to check
'again later.
Application.OnTime (Now + TimeValue(TimeCheckDelay)), "CheckTime"
End If
End Sub

The code above is set to close the workbook without saving changes if it is
idle for more than 58 minutes. It will check every 10 minutes.

Answer #2    Answered By: Hugo Williams     Answered On: Nov 14

You might like to restart the timer on Workbook_SheetSelectionChange events
as well as (or simply instead of) change events. This would allow you to
keep it "alive" simply by clicking a cell, rather than changing it.

Didn't find what you were looking for? Find more on Time out and exit Or get search suggestion and latest updates.