This answer is provided by Tushar Mehta
 

ID: 7

Category: Excel Automation

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

Answer: This version is far more efficient than the first one in the Extended links section below. There is no reason to reset the timer event each time there is some activity. Instead, the called procedure checks if there has been any activity in the last n minutes. If so, it simply reschedules itself. If not, it saves and closes the workbook.

However, note that this process will not work if Excel is in 'edit' mode, i.e., editing the contents of a cell. Only when the program exits edit mode will it run the scheduled procedure, Check_Inactivity. For a workaround -- though, that has other problems -- see How do I close a workbook that hasn't been used in 5 minutes? (works in edit mode).

In the ThisWorkbook module:

Option Explicit
Private Sub Workbook_Open()
    LastActivityTime = Now()
    Check_Inactivity
    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
And, in a standard module
Option Explicit
Public LastActivityTime As Date
Sub Check_Inactivity()
    Const Inactivity_Delay As Date = #12:05:00 AM#
    If LastActivityTime + Inactivity_Delay < Now() Then
        ThisWorkbook.Close True
    Else
        Application.OnTime LastActivityTime + Inactivity_Delay, "Check_Inactivity"
        End If
    End Sub

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


Website material copyright 2003-2006 TM Faculty Associates