Close workbook after a period of inactivity-

Posted by Lewis on November 25, 2001 9:18 AM

Hi,

Apologies for reposting this query but I am unable to find the posting that someone else made on the subject.

I want to run a macro and close a workbook if there are no entries etc made for a period of time.

Thanks in advance

Lewis

Posted by Bib on November 26, 2001 5:35 AM

Hi Lewis,

Try this:
Paste this code in module1:

Const idleTime = 30 'seconds
Public isActive As Boolean
Dim Start

Sub StartTimer()

Start = Timer
Do While Timer &LT; Start + idleTime
DoEvents
Loop
If Not isActive Then
If MsgBox("Idle for " & idleTime & " seconds. Close workbook ?", vbQuestion + vbYesNo) = vbYes Then
ActiveWorkbook.Close
Else
isActive = False
StartTimer
End If
Else
isActive = False
StartTimer
End If

End Sub

Paste this code in ThisWorkbook:

Private Sub Workbook_Open()
Module1.isActive = True
StartTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Module1.isActive = True
StartTimer
End Sub

Hope it helps...