This answer is provided by Tushar Mehta
 

ID: 8

Category: Excel Automation

Question: How do I close a workbook that hasn't been used in 5 minutes? (works in edit mode)

Answer: This uses the Windows timer events, SetTimer and KillTimer to schedule an inactivity check for an Excel workbook. The advantage is that it works even if Excel is in edit mode. The disadvantage is that if Excel is indeed in edit mode, the workbook is saved; but, then, Excel crashes! This is true at least with Excel 2002.

For an alternative version, see the link below.

In the ThisWorkbook module:

Option Explicit
Private Sub Workbook_Open()
    LastActivityTime = Now()
    Check_Inactivity_SysTimer
    End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    LastActivityTime = Now()
    End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    LastActivityTime = Now()
    End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    LastActivityTime = Now()
    End Sub
In one standard module:
Option Explicit
Declare Function SetTimer Lib "user32" ( _
    ByVal hwnd As Long, ByVal nIDEvent As Long, _
    ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Declare Function KillTimer Lib "user32" ( _
    ByVal hwnd As Long, ByVal nIDEvent As Long) As Long

Public TimerIsOn As Boolean, TimerID As Long

Sub TimerOn(aDate As Date, ProcAddress)
    Dim TimeInSecs As Long
    TimeInSecs = Hour(aDate) * 3600 + Minute(aDate) * 60 + Second(aDate)
    If Not TimerIsOn Then
        TimerID = SetTimer(0, 0, 1000 * TimeInSecs, ProcAddress)
        If TimerID = 0 Then
            MsgBox "Unable to create the countdown timer", vbCritical + vbOKOnly, "Error"
            Exit Sub
            End If
        TimerIsOn = True
    Else
        MsgBox "Timer is already on"
        End If
    End Sub
Sub TimerOff()
    If TimerIsOn Then
        TimerID = KillTimer(0, TimerID)
        If TimerID = 0 Then
            MsgBox "Unable to stop the countdown timer", vbCritical + vbOKOnly, "Error"
            End If
        TimerIsOn = False
    Else
        'MsgBox "Timer is already off!"
        End If
    End Sub
In another standard module:
Option Explicit
Public LastActivityTime As Date

Sub Check_Inactivity_SysTimer()
    Const Inactivity_Delay As Date = #12:00:10 AM#
    TimerOff
    If LastActivityTime + Inactivity_Delay < Now() Then
        Application.OnTime Now(), "closeWB"
        'need the above statement because just closing the WB crashes XL
        
    Else
        TimerOn LastActivityTime + Inactivity_Delay - Now(), _
            AddressOf Check_Inactivity_SysTimer
        End If
    End Sub
Sub closeWB()
    ThisWorkbook.Close True
    End Sub

ExtendedLink: http://www.tmehta.com/tmxl/dbfind_byid.asp?id=7


Website material copyright 2003-2006 TM Faculty Associates