Nyanko
This module was created for an application other than Excel. I edited it and it appears to work fine. Download the example and read the notes contained within the code. Let me know if you have any problems...
OnTimePlus.zip
Example usage utilizing workbook open.
<table width="100%" border="1" bgcolor="White" style="filter
rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New> <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_Open()
MsgBox "This workbook will automatically save and close if there are 5 consecutive seconds of inactivity. <font color="#0000A0">If</font> this is the only workbook open, Excel will quit as well."
RunOnTimePlus
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Sub</font> RunOnTimePlus()
<font color="#0000A0">Dim</font> OnTimeArgs <font color="#0000A0">As</font> OnTimeArguments
<font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">GoTo</font> Err_Example
<font color="#0000A0">With</font> OnTimeArgs
.TimeOutOn = OnUserIdle
.ProcedureName = "ThisWorkbook.TimedOut"
.CheckIntervalSeconds = 1
.Seconds = 5
<font color="#0000A0">End</font> <font color="#0000A0">With</font>
StartOnTimePlus OnTimeArgs
<font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>
Err_Example:
MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & Err.Description, , Err.Source
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Friend</font> <font color="#0000A0">Sub</font> TimedOut()
<font color="#0000A0">If</font> Ok2CloseApp <font color="#0000A0">Then</font>
Me.Save
Application.Quit
<font color="#0000A0">Else</font>
Me.Close <font color="#0000A0">True</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Function</font> Ok2CloseApp() <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
<font color="#0000A0">Dim</font> wb <font color="#0000A0">As</font> Workbook, WBCnt <font color="#0000A0">As</font> <font color="#0000A0">Integer</font>
<font color="#0000A0">For</font> <font color="#0000A0">Each</font> wb <font color="#0000A0">In</font> Application.Workbooks
<font color="#0000A0">If</font> wb.Path <> Application.StartupPath <font color="#0000A0">Or</font> <font color="#0000A0">Not</font> wb.IsAddin <font color="#0000A0">Then</font>
WBCnt = WBCnt + 1
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">Next</font>
Ok2CloseApp = (WBCnt = 1)
<font color="#0000A0">End</font> <font color="#0000A0">Function</font>
</FONT></td></tr></table><button onclick='document.all("1122007133945295").value=document.all("1122007133945295").value.replace(/<br \/>\s\s/g,"");document.all("1122007133945295").value=document.all("1122007133945295").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("1122007133945295").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="1122007133945295" wrap="virtual">
Option Explicit
Private Sub Workbook_Open()
MsgBox "This workbook will automatically save and close if there are 5 consecutive seconds of inactivity. If this is the only workbook open, Excel will quit as well."
RunOnTimePlus
End Sub
Sub RunOnTimePlus()
Dim OnTimeArgs As OnTimeArguments
On Error GoTo Err_Example
With OnTimeArgs
.TimeOutOn = OnUserIdle
.ProcedureName = "ThisWorkbook.TimedOut"
.CheckIntervalSeconds = 1
.Seconds = 5
End With
StartOnTimePlus OnTimeArgs
Exit Sub
Err_Example:
MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & Err.Description, , Err.Source
End Sub
Friend Sub TimedOut()
If Ok2CloseApp Then
Me.Save
Application.Quit
Else
Me.Close True
End If
End Sub
Private Function Ok2CloseApp() As Boolean
Dim wb As Workbook, WBCnt As Integer
For Each wb In Application.Workbooks
If wb.Path <> Application.StartupPath Or Not wb.IsAddin Then
WBCnt = WBCnt + 1
End If
Next
Ok2CloseApp = (WBCnt = 1)
End Function</textarea>
In a standard module.
<table width="100%" border="1" bgcolor="White" style="filter
rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New> <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>
<font color="#008000">'StopOnTimePlus comment it out</font>
<font color="#008000">' to initiate a timeout from another application, use the SetProp API function along with the Desktop's hWnd</font>
<font color="#008000">' Example</font>
<font color="#008000">' Option Explicit</font>
<font color="#008000">'</font>
<font color="#008000">' Private Declare Function SetProp Lib "user32" Alias "SetPropA" (ByVal hwnd As Long, ByVal lpString As String, ByVal hData As Long) As Long</font>
<font color="#008000">' Private Declare Function GetDesktopWindow Lib "user32" () As Long</font>
<font color="#008000">'</font>
<font color="#008000">' Sub Example()</font>
<font color="#008000">' SetProp GetDesktopWindow, "OnTimePlus.xls", CLng(True)</font>
<font color="#008000">' End Sub</font>
<font color="#008000">' will fire the timeout in this workbook</font>
<font color="#008000">' the timeout may not occur immediately the delay depends on the value you</font>
<font color="#008000">' assign to OnTimeArguments.CheckIntervalSeconds</font>
<font color="#008000">'</font>
<font color="#008000">' order of precedence:</font>
<font color="#008000">' 1. OnTimeOutFromExternal</font>
<font color="#008000">' 2. OnTime</font>
<font color="#008000">' 3. OnWorkBookDeactivation (and/or) OnApplicationDeactivation (and/or) OnUserIdle</font>
<font color="#008000">'</font>
<font color="#008000">' the min timeout is 1 second</font>
<font color="#008000">'</font>
<font color="#008000">' if argument AtDateTime is assigned a value then OnTimeFlag = True</font>
<font color="#008000">'</font>
<font color="#008000">' if OnTimeFlag = True then argument AtDateTime must contain a valid date</font>
<font color="#008000">'</font>
<font color="#008000">' OnTimeArguments.CheckIntervalSeconds is the interval the code checks for time out conditions</font>
<font color="#008000">'</font>
<font color="#008000">' Each call to OnTimePlus supercedes previous calls</font>
<font color="#008000">'</font>
<font color="#008000">' You can call OnTimePlus from your timeout procedure to restart it automatically</font>
<font color="#008000">'</font>
<font color="#008000">' OnUserIdle is an application wide notification, not system wide, and</font>
<font color="#008000">' relates to keyboard and mouse activity only</font>
<font color="#008000">'</font>
<font color="#008000">' the Function TimedOut() is the default function. I recommend that you assign a valid</font>
<font color="#008000">' procedure name to the OnTimeArguments.ProcedureName arg. This procedure should be</font>
<font color="#008000">' located elsewhere in your project. If it is locted in a public object module such</font>
<font color="#008000">' as a workbook or worksheet class, make sure you qualify it as a member using the codename</font>
<font color="#008000">' "ThisWorkbook.MyProcedure" or "Sheet1.MyProcedure"</font>
<font color="#008000">'</font>
<font color="#008000">' TimeOutOn may be any combination of TimeOutType enum values.</font>
<font color="#008000">' Explanations</font>
<font color="#008000">'</font>
<font color="#008000">' TimeOutOn = OnTime + OnApplicationDeactivation + OnUserIdle</font>
<font color="#008000">' will time out if...</font>
<font color="#008000">' 1. the application loses focus or is minimized for the amount of time defined in Hours, Minutes, or Seconds or</font>
<font color="#008000">' 2. no user activity is detected for the amount of time defined in Hours, Minutes, or Seconds or</font>
<font color="#008000">' 3. the AtDateTime has been equaled by the system time</font>
<font color="#008000">'</font>
<font color="#008000">' TimeOutOn = OnTime</font>
<font color="#008000">' will time out if...</font>
<font color="#008000">' 1. the AtDateTime has been equaled by the system time and is the same as Application.OnTime</font>
<font color="#008000">'</font>
<font color="#008000">' TimeOutOn = OnTimeOutFromExternal</font>
<font color="#008000">' will time out if...</font>
<font color="#008000">' 1. simply allows the workbook to be timed out from an another</font>
<font color="#008000">' procedure located within the host application or an external application</font>
<font color="#008000">' OnTimeArguments.CheckIntervalSeconds should be set to a lower value</font>
<font color="#008000">'</font>
<font color="#008000">' TimeOutOn = OnTime</font>
<font color="#008000">' will time out if...</font>
<font color="#008000">' 1. the AtDateTime has been equaled by the system time</font>
<font color="#008000">'</font>
<font color="#008000">' TimeOutOn = OnApplicationDeactivation + OnWorkBookDeactivation + OnUserIdle + OnTimeOutFromExternal</font>
<font color="#008000">' will time out if...</font>
<font color="#008000">' 1. the application loses focus or is minimized for the amount of time defined in Hours, Minutes, or Seconds or</font>
<font color="#008000">' 2. no user activity is detected for the amount of time defined in Hours, Minutes, or Seconds or</font>
<font color="#008000">' 3. the workbook is deactivated for the amount of time defined in Hours, Minutes, or Seconds or</font>
<font color="#008000">' 4. a timeout command is sent from another source</font>
<font color="#0000A0">Public</font> <font color="#0000A0">Enum</font> TimeOutType
OnTimeOutFromExternal = 1
OnTime = 2
OnWorkBookDeactivation = 4
OnApplicationDeactivation = 8
OnUserIdle = 16
<font color="#0000A0">End</font> <font color="#0000A0">Enum</font>
<font color="#0000A0">Public</font> <font color="#0000A0">Type</font> OnTimeArguments
TimeOutOn <font color="#0000A0">As</font> TimeOutType
AtDateTime <font color="#0000A0">As</font> <font color="#0000A0">Date</font>
Hours <font color="#0000A0">As</font> <font color="#0000A0">Double</font>
Minutes <font color="#0000A0">As</font> <font color="#0000A0">Double</font>
Seconds <font color="#0000A0">As</font> <font color="#0000A0">Double</font>
CheckIntervalSeconds <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
ProcedureName <font color="#0000A0">As</font> <font color="#0000A0">String</font>
<font color="#0000A0">End</font> <font color="#0000A0">Type</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Type</font> LASTINPUTINFO
cbSize <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
dwTime <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">End</font> <font color="#0000A0">Type</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> GetLastInputInfo <font color="#0000A0">Lib</font> "user32.dll" (ByRef plii <font color="#0000A0">As</font> LASTINPUTINFO) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> GetTickCount <font color="#0000A0">Lib</font> "kernel32" () <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> SetTimer <font color="#0000A0">Lib</font> "user32" (ByVal hwnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> nIDEvent <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> uElapse <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> lpTimerFunc <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> KillTimer <font color="#0000A0">Lib</font> "user32" (ByVal hwnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> nIDEvent <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> FindWindow <font color="#0000A0">Lib</font> "user32" <font color="#0000A0">Alias</font> "FindWindowA" (ByVal lpClassName <font color="#0000A0">As</font> String, <font color="#0000A0">ByVal</font> lpWindowName <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> GetForegroundWindow <font color="#0000A0">Lib</font> "user32" () <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> GetProp <font color="#0000A0">Lib</font> "user32" <font color="#0000A0">Alias</font> "GetPropA" (ByVal hwnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> lpString <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> SetProp <font color="#0000A0">Lib</font> "user32" <font color="#0000A0">Alias</font> "SetPropA" (ByVal hwnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> lpString <font color="#0000A0">As</font> String, <font color="#0000A0">ByVal</font> hData <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> RemoveProp <font color="#0000A0">Lib</font> "user32" <font color="#0000A0">Alias</font> "RemovePropA" (ByVal hwnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> lpString <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> GetDesktopWindow <font color="#0000A0">Lib</font> "user32" () <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Const</font> MAXIMUM_INTERVAL_SECONDS <font color="#0000A0">As</font> <font color="#0000A0">Long</font> = 86400
<font color="#0000A0">Private</font> <font color="#0000A0">Const</font> MINIMUM_INTERVAL_SECONDS <font color="#0000A0">As</font> <font color="#0000A0">Long</font> = 1
<font color="#0000A0">Private</font> TimerId <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> TimerCheckIntervalMilliseconds <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> TimeOutOnTime(1) <font color="#0000A0">As</font> <font color="#0000A0">Date</font>
<font color="#0000A0">Private</font> TotalSeconds <font color="#0000A0">As</font> <font color="#0000A0">Double</font>
<font color="#0000A0">Private</font> RunProcedureName <font color="#0000A0">As</font> <font color="#0000A0">String</font>
<font color="#0000A0">Private</font> OnTimeOutFromExternalFlag <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
<font color="#0000A0">Private</font> OnTimeFlag <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
<font color="#0000A0">Private</font> OnWorkBookDeactivationFlag <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
<font color="#0000A0">Private</font> OnApplicationDeactivationFlag <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
<font color="#0000A0">Private</font> OnUserIdleFlag <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
<font color="#0000A0">Private</font> LastInputTickCount <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> AppHwnd <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> OnTimeOutFromExternalPropName <font color="#0000A0">As</font> <font color="#0000A0">String</font>
<font color="#0000A0">Sub</font> Examples()
<font color="#0000A0">Dim</font> OnTimeArgs <font color="#0000A0">As</font> OnTimeArguments
<font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">GoTo</font> Err_Example
<font color="#008000">' 'will shut down the workbook if no activity is detected in 2 hours and 30 minutes</font>
<font color="#008000">' 'or at midnight tommorrow. Whichever comes first. Will check for these conditions</font>
<font color="#008000">' 'every thirty seconds. The procedure named "TimedOut" located in ThisWorkbook will fire</font>
<font color="#008000">' With OnTimeArgs</font>
<font color="#008000">' .TimeOutOn = OnUserIdle + OnTime</font>
<font color="#008000">' .AtDateTime = Date + 1</font>
<font color="#008000">' .ProcedureName = "ThisWorkbook.TimedOut"</font>
<font color="#008000">' .CheckIntervalSeconds = 30</font>
<font color="#008000">' .Hours = 2</font>
<font color="#008000">' .Minutes = 30</font>
<font color="#008000">' End With</font>
<font color="#008000">'The procedure named "TimedOut" located in ThisWorkbook will fire if</font>
<font color="#008000">'there are 10 seconds of inactivity</font>
<font color="#008000">'the workbook will automatically save and close</font>
<font color="#0000A0">With</font> OnTimeArgs
.TimeOutOn = OnUserIdle
.ProcedureName = "ThisWorkbook.TimedOut"
.CheckIntervalSeconds = 1
.Seconds = 10
<font color="#0000A0">End</font> <font color="#0000A0">With</font>
StartOnTimePlus OnTimeArgs
<font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>
Err_Example:
MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & Err.Description, , Err.Source
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Public</font> <font color="#0000A0">Function</font> StopOnTimePlus() <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
StopOnTimePlus = <font color="#0000A0">Not</font> (KillTimer(0, TimerId) = 0)
<font color="#0000A0">End</font> <font color="#0000A0">Function</font>
<font color="#0000A0">Public</font> <font color="#0000A0">Function</font> StartOnTimePlus(Args <font color="#0000A0">As</font> OnTimeArguments) <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
<font color="#0000A0">Dim</font> MinimumInterval <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">GoTo</font> Err_OnTimePlus
<font color="#0000A0">If</font> Args.TimeOutOn = 0 <font color="#0000A0">Then</font>
Err.Raise 10004, "Sub OnTimePlus", "Argument ""OnTimeArguments.TimeOutOn"" type must contain one or more assignments."
<font color="#0000A0">Exit</font> <font color="#0000A0">Function</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
ResetVariables
<font color="#0000A0">If</font> TimerId <> 0 <font color="#0000A0">Then</font>
StopOnTimePlus
TimerId = 0
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">If</font> Args.CheckIntervalSeconds < MINIMUM_INTERVAL_SECONDS <font color="#0000A0">Then</font> Args.CheckIntervalSeconds = MINIMUM_INTERVAL_SECONDS
<font color="#0000A0">If</font> Args.CheckIntervalSeconds > MAXIMUM_INTERVAL_SECONDS <font color="#0000A0">Then</font> Args.CheckIntervalSeconds = MAXIMUM_INTERVAL_SECONDS
OnTimeOutFromExternalFlag = Args.TimeOutOn <font color="#0000A0">And</font> OnTimeOutFromExternal
OnTimeFlag = Args.TimeOutOn <font color="#0000A0">And</font> OnTime
OnWorkBookDeactivationFlag = Args.TimeOutOn <font color="#0000A0">And</font> OnWorkBookDeactivation
OnApplicationDeactivationFlag = Args.TimeOutOn <font color="#0000A0">And</font> OnApplicationDeactivation
OnUserIdleFlag = Args.TimeOutOn <font color="#0000A0">And</font> OnUserIdle
<font color="#0000A0">If</font> CDbl(Args.AtDateTime) > 0 <font color="#0000A0">Then</font> OnTimeFlag = <font color="#0000A0">True</font>
<font color="#0000A0">If</font> OnTimeFlag <font color="#0000A0">And</font> Args.AtDateTime < Now <font color="#0000A0">Then</font>
Err.Raise 10000, "Sub OnTimePlus", "Argument ""AtDateTime"" must be greater than the current date and time."
<font color="#0000A0">Exit</font> <font color="#0000A0">Function</font>
<font color="#0000A0">ElseIf</font> OnTimeFlag = <font color="#0000A0">True</font> <font color="#0000A0">Then</font>
TimeOutOnTime(0) = Args.AtDateTime
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">If</font> (Args.Hours + Args.Minutes + Args.Seconds <= 0) <font color="#0000A0">Then</font> Args.Seconds = 1
TotalSeconds = (Args.Hours * 36000) + (Args.Minutes * 60) + (Args.Seconds)
TimeOutOnTime(1) = DateAdd("s", TotalSeconds, Now)
<font color="#0000A0">If</font> <font color="#0000A0">Not</font> OnTimeFlag <font color="#0000A0">Then</font> TimeOutOnTime(0) = TimeOutOnTime(1)
MinimumInterval = Application.WorksheetFunction.Min(DateDiff("s", Now, TimeOutOnTime(0)), TotalSeconds)
<font color="#0000A0">If</font> MinimumInterval < Args.CheckIntervalSeconds <font color="#0000A0">Then</font> Args.CheckIntervalSeconds = Int(MinimumInterval / 10)
<font color="#0000A0">If</font> Args.CheckIntervalSeconds = 0 <font color="#0000A0">Then</font> Args.CheckIntervalSeconds = 1
RunProcedureName = Args.ProcedureName
AppHwnd = FindWindow("XLMAIN", Application.Caption)
<font color="#0000A0">If</font> OnTimeOutFromExternalFlag <font color="#0000A0">Then</font>
OnTimeOutFromExternalPropName = ThisWorkbook.Name
SetProp GetDesktopWindow, OnTimeOutFromExternalPropName, CLng(False)
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
TimeOutOnTime(1) = Now
TimerCheckIntervalMilliseconds = (Args.CheckIntervalSeconds * 800)
LastInputTickCount = GetTickCount
ResetTimer
StartOnTimePlus = <font color="#0000A0">True</font>
<font color="#0000A0">Exit</font> <font color="#0000A0">Function</font>
Err_OnTimePlus:
<font color="#0000A0">If</font> Err.Number = 6 <font color="#0000A0">Then</font>
Err.Raise 10002, "Sub OnTimePlus", "Invalid argument."
<font color="#0000A0">ElseIf</font> Err.Number = 10000 <font color="#0000A0">Or</font> Err.Number = 10004 <font color="#0000A0">Then</font>
Err.Raise Err.Number, Err.Source, Err.Description
<font color="#0000A0">Exit</font> <font color="#0000A0">Function</font>
<font color="#0000A0">Else</font>
Debug.Print "Unhandled error in <font color="#0000A0">Function</font> OnTimePlus" & Err.Number & ", " & Err.Description
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">Function</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Function</font> ResetVariables()
TimerId = 0
TimerCheckIntervalMilliseconds = 0
TimeOutOnTime(0) = 0
TimeOutOnTime(1) = 0
TotalSeconds = 0
RunProcedureName = ""
OnTimeFlag = <font color="#0000A0">False</font>
OnWorkBookDeactivationFlag = <font color="#0000A0">False</font>
OnApplicationDeactivationFlag = <font color="#0000A0">False</font>
OnUserIdleFlag = <font color="#0000A0">False</font>
RemoveProp GetDesktopWindow, OnTimeOutFromExternalPropName
<font color="#0000A0">End</font> <font color="#0000A0">Function</font>
<font color="#0000A0">Function</font> ResetTimer()
TimerId = SetTimer(0, TimerId, TimerCheckIntervalMilliseconds, <font color="#0000A0">AddressOf</font> CheckTimeOutStatus)
<font color="#0000A0">End</font> <font color="#0000A0">Function</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Function</font> CheckTimeOutStatus(ByVal hwnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> message <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> idTimer <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> dwTime <font color="#0000A0">As</font> Long)
<font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">GoTo</font> Err_CheckTimeOutStatus
<font color="#0000A0">If</font> CBool(GetProp(GetDesktopWindow, OnTimeOutFromExternalPropName)) <font color="#0000A0">Then</font>
TimedOut
<font color="#0000A0">Exit</font> <font color="#0000A0">Function</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">If</font> OnTimeFlag <font color="#0000A0">And</font> (Now >= TimeOutOnTime(0)) <font color="#0000A0">Then</font>
TimedOut
<font color="#0000A0">Exit</font> <font color="#0000A0">Function</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">If</font> OnApplicationDeactivationFlag <font color="#0000A0">Then</font>
<font color="#0000A0">If</font> GetForegroundWindow = AppHwnd <font color="#0000A0">And</font> Application.WindowState <> xlMinimized <font color="#0000A0">Then</font>
<font color="#0000A0">If</font> <font color="#0000A0">Not</font> OnUserIdleFlag <font color="#0000A0">Then</font> TimeOutOnTime(1) = Now
<font color="#0000A0">Else</font>
<font color="#0000A0">If</font> HasTimedOut <font color="#0000A0">Then</font>
TimedOut
<font color="#0000A0">Exit</font> <font color="#0000A0">Function</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">If</font> OnWorkBookDeactivationFlag <font color="#0000A0">Then</font>
<font color="#0000A0">If</font> ThisWorkbook <font color="#0000A0">Is</font> ActiveWorkbook <font color="#0000A0">Then</font>
TimeOutOnTime(1) = Now
<font color="#0000A0">Else</font>
<font color="#0000A0">If</font> HasTimedOut <font color="#0000A0">Then</font>
TimedOut
<font color="#0000A0">Exit</font> <font color="#0000A0">Function</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">If</font> OnUserIdleFlag <font color="#0000A0">Then</font>
<font color="#0000A0">Dim</font> LastInput <font color="#0000A0">As</font> LASTINPUTINFO
LastInput.cbSize = Len(LastInput)
<font color="#0000A0">If</font> GetLastInputInfo(LastInput) <> 0 <font color="#0000A0">Then</font>
<font color="#0000A0">If</font> LastInput.dwTime <> LastInputTickCount <font color="#0000A0">Then</font>
TimeOutOnTime(1) = Now
<font color="#0000A0">Else</font>
<font color="#0000A0">If</font> HasTimedOut <font color="#0000A0">Then</font>
TimedOut
<font color="#0000A0">Exit</font> <font color="#0000A0">Function</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
LastInputTickCount = LastInput.dwTime
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">Exit</font> <font color="#0000A0">Function</font>
Err_CheckTimeOutStatus:
StopOnTimePlus
Debug.Print "Unhandled error in <font color="#0000A0">Function</font> CheckTimeOutStatus," & Err.Number & ", " & Err.Description
<font color="#0000A0">End</font> <font color="#0000A0">Function</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Function</font> HasTimedOut() <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
<font color="#0000A0">If</font> DateDiff("s", TimeOutOnTime(1), Now) >= TotalSeconds <font color="#0000A0">Then</font>
HasTimedOut = <font color="#0000A0">True</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">Function</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Function</font> TimedOut()
<font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">Resume</font> <font color="#0000A0">Next</font>
StopOnTimePlus
<font color="#0000A0">If</font> RunProcedureName <> "" <font color="#0000A0">Then</font>
Application.OnTime Now, RunProcedureName
<font color="#0000A0">If</font> Err.Number = 1004 <font color="#0000A0">Then</font>
Err.Raise 10003, "Function TimedOut", Err.Description
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
ResetVariables
<font color="#0000A0">End</font> <font color="#0000A0">Function</font>
</FONT></td></tr></table><button onclick='document.all("1122007134033160").value=document.all("1122007134033160").value.replace(/<br \/>\s\s/g,"");document.all("1122007134033160").value=document.all("1122007134033160").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("1122007134033160").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="1122007134033160" wrap="virtual">
Option Explicit
'StopOnTimePlus comment it out
' to initiate a timeout from another application, use the SetProp API function along with the Desktop's hWnd
' Example
' Option Explicit
'
' Private Declare Function SetProp Lib "user32" Alias "SetPropA" (ByVal hwnd As Long, ByVal lpString As String, ByVal hData As Long) As Long
' Private Declare Function GetDesktopWindow Lib "user32" () As Long
'
' Sub Example()
' SetProp GetDesktopWindow, "OnTimePlus.xls", CLng(True)
' End Sub
' will fire the timeout in this workbook
' the timeout may not occur immediately the delay depends on the value you
' assign to OnTimeArguments.CheckIntervalSeconds
'
' order of precedence:
' 1. OnTimeOutFromExternal
' 2. OnTime
' 3. OnWorkBookDeactivation (and/or) OnApplicationDeactivation (and/or) OnUserIdle
'
' the min timeout is 1 second
'
' if argument AtDateTime is assigned a value then OnTimeFlag = True
'
' if OnTimeFlag = True then argument AtDateTime must contain a valid date
'
' OnTimeArguments.CheckIntervalSeconds is the interval the code checks for time out conditions
'
' Each call to OnTimePlus supercedes previous calls
'
' You can call OnTimePlus from your timeout procedure to restart it automatically
'
' OnUserIdle is an application wide notification, not system wide, and
' relates to keyboard and mouse activity only
'
' the Function TimedOut() is the default function. I recommend that you assign a valid
' procedure name to the OnTimeArguments.ProcedureName arg. This procedure should be
' located elsewhere in your project. If it is locted in a public object module such
' as a workbook or worksheet class, make sure you qualify it as a member using the codename
' "ThisWorkbook.MyProcedure" or "Sheet1.MyProcedure"
'
' TimeOutOn may be any combination of TimeOutType enum values.
' Explanations
'
' TimeOutOn = OnTime + OnApplicationDeactivation + OnUserIdle
' will time out if...
' 1. the application loses focus or is minimized for the amount of time defined in Hours, Minutes, or Seconds or
' 2. no user activity is detected for the amount of time defined in Hours, Minutes, or Seconds or
' 3. the AtDateTime has been equaled by the system time
'
' TimeOutOn = OnTime
' will time out if...
' 1. the AtDateTime has been equaled by the system time and is the same as Application.OnTime
'
' TimeOutOn = OnTimeOutFromExternal
' will time out if...
' 1. simply allows the workbook to be timed out from an another
' procedure located within the host application or an external application
' OnTimeArguments.CheckIntervalSeconds should be set to a lower value
'
' TimeOutOn = OnTime
' will time out if...
' 1. the AtDateTime has been equaled by the system time
'
' TimeOutOn = OnApplicationDeactivation + OnWorkBookDeactivation + OnUserIdle + OnTimeOutFromExternal
' will time out if...
' 1. the application loses focus or is minimized for the amount of time defined in Hours, Minutes, or Seconds or
' 2. no user activity is detected for the amount of time defined in Hours, Minutes, or Seconds or
' 3. the workbook is deactivated for the amount of time defined in Hours, Minutes, or Seconds or
' 4. a timeout command is sent from another source
Public Enum TimeOutType
OnTimeOutFromExternal = 1
OnTime = 2
OnWorkBookDeactivation = 4
OnApplicationDeactivation = 8
OnUserIdle = 16
End Enum
Public Type OnTimeArguments
TimeOutOn As TimeOutType
AtDateTime As Date
Hours As Double
Minutes As Double
Seconds As Double
CheckIntervalSeconds As Long
ProcedureName As String
End Type
Private Type LASTINPUTINFO
cbSize As Long
dwTime As Long
End Type
Private Declare Function GetLastInputInfo Lib "user32.dll" (ByRef plii As LASTINPUTINFO) As Long
Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetForegroundWindow Lib "user32" () As Long
Private Declare Function GetProp Lib "user32" Alias "GetPropA" (ByVal hwnd As Long, ByVal lpString As String) As Long
Private Declare Function SetProp Lib "user32" Alias "SetPropA" (ByVal hwnd As Long, ByVal lpString As String, ByVal hData As Long) As Long
Private Declare Function RemoveProp Lib "user32" Alias "RemovePropA" (ByVal hwnd As Long, ByVal lpString As String) As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long
Private Const MAXIMUM_INTERVAL_SECONDS As Long = 86400
Private Const MINIMUM_INTERVAL_SECONDS As Long = 1
Private TimerId As Long
Private TimerCheckIntervalMilliseconds As Long
Private TimeOutOnTime(1) As Date
Private TotalSeconds As Double
Private RunProcedureName As String
Private OnTimeOutFromExternalFlag As Boolean
Private OnTimeFlag As Boolean
Private OnWorkBookDeactivationFlag As Boolean
Private OnApplicationDeactivationFlag As Boolean
Private OnUserIdleFlag As Boolean
Private LastInputTickCount As Long
Private AppHwnd As Long
Private OnTimeOutFromExternalPropName As String
Sub Examples()
Dim OnTimeArgs As OnTimeArguments
On Error GoTo Err_Example
' 'will shut down the workbook if no activity is detected in 2 hours and 30 minutes
' 'or at midnight tommorrow. Whichever comes first. Will check for these conditions
' 'every thirty seconds. The procedure named "TimedOut" located in ThisWorkbook will fire
' With OnTimeArgs
' .TimeOutOn = OnUserIdle + OnTime
' .AtDateTime = Date + 1
' .ProcedureName = "ThisWorkbook.TimedOut"
' .CheckIntervalSeconds = 30
' .Hours = 2
' .Minutes = 30
' End With
'The procedure named "TimedOut" located in ThisWorkbook will fire if
'there are 10 seconds of inactivity
'the workbook will automatically save and close
With OnTimeArgs
.TimeOutOn = OnUserIdle
.ProcedureName = "ThisWorkbook.TimedOut"
.CheckIntervalSeconds = 1
.Seconds = 10
End With
StartOnTimePlus OnTimeArgs
Exit Sub
Err_Example:
MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & Err.Description, , Err.Source
End Sub
Public Function StopOnTimePlus() As Boolean
StopOnTimePlus = Not (KillTimer(0, TimerId) = 0)
End Function
Public Function StartOnTimePlus(Args As OnTimeArguments) As Boolean
Dim MinimumInterval As Long
On Error GoTo Err_OnTimePlus
If Args.TimeOutOn = 0 Then
Err.Raise 10004, "Sub OnTimePlus", "Argument ""OnTimeArguments.TimeOutOn"" type must contain one or more assignments."
Exit Function
End If
ResetVariables
If TimerId <> 0 Then
StopOnTimePlus
TimerId = 0
End If
If Args.CheckIntervalSeconds < MINIMUM_INTERVAL_SECONDS Then Args.CheckIntervalSeconds = MINIMUM_INTERVAL_SECONDS
If Args.CheckIntervalSeconds > MAXIMUM_INTERVAL_SECONDS Then Args.CheckIntervalSeconds = MAXIMUM_INTERVAL_SECONDS
OnTimeOutFromExternalFlag = Args.TimeOutOn And OnTimeOutFromExternal
OnTimeFlag = Args.TimeOutOn And OnTime
OnWorkBookDeactivationFlag = Args.TimeOutOn And OnWorkBookDeactivation
OnApplicationDeactivationFlag = Args.TimeOutOn And OnApplicationDeactivation
OnUserIdleFlag = Args.TimeOutOn And OnUserIdle
If CDbl(Args.AtDateTime) > 0 Then OnTimeFlag = True
If OnTimeFlag And Args.AtDateTime < Now Then
Err.Raise 10000, "Sub OnTimePlus", "Argument ""AtDateTime"" must be greater than the current date and time."
Exit Function
ElseIf OnTimeFlag = True Then
TimeOutOnTime(0) = Args.AtDateTime
End If
If (Args.Hours + Args.Minutes + Args.Seconds <= 0) Then Args.Seconds = 1
TotalSeconds = (Args.Hours * 36000) + (Args.Minutes * 60) + (Args.Seconds)
TimeOutOnTime(1) = DateAdd("s", TotalSeconds, Now)
If Not OnTimeFlag Then TimeOutOnTime(0) = TimeOutOnTime(1)
MinimumInterval = Application.WorksheetFunction.Min(DateDiff("s", Now, TimeOutOnTime(0)), TotalSeconds)
If MinimumInterval < Args.CheckIntervalSeconds Then Args.CheckIntervalSeconds = Int(MinimumInterval / 10)
If Args.CheckIntervalSeconds = 0 Then Args.CheckIntervalSeconds = 1
RunProcedureName = Args.ProcedureName
AppHwnd = FindWindow("XLMAIN", Application.Caption)
If OnTimeOutFromExternalFlag Then
OnTimeOutFromExternalPropName = ThisWorkbook.Name
SetProp GetDesktopWindow, OnTimeOutFromExternalPropName, CLng(False)
End If
TimeOutOnTime(1) = Now
TimerCheckIntervalMilliseconds = (Args.CheckIntervalSeconds * 800)
LastInputTickCount = GetTickCount
ResetTimer
StartOnTimePlus = True
Exit Function
Err_OnTimePlus:
If Err.Number = 6 Then
Err.Raise 10002, "Sub OnTimePlus", "Invalid argument."
ElseIf Err.Number = 10000 Or Err.Number = 10004 Then
Err.Raise Err.Number, Err.Source, Err.Description
Exit Function
Else
Debug.Print "Unhandled error in Function OnTimePlus" & Err.Number & ", " & Err.Description
End If
End Function
Private Function ResetVariables()
TimerId = 0
TimerCheckIntervalMilliseconds = 0
TimeOutOnTime(0) = 0
TimeOutOnTime(1) = 0
TotalSeconds = 0
RunProcedureName = ""
OnTimeFlag = False
OnWorkBookDeactivationFlag = False
OnApplicationDeactivationFlag = False
OnUserIdleFlag = False
RemoveProp GetDesktopWindow, OnTimeOutFromExternalPropName
End Function
Function ResetTimer()
TimerId = SetTimer(0, TimerId, TimerCheckIntervalMilliseconds, AddressOf CheckTimeOutStatus)
End Function
Private Function CheckTimeOutStatus(ByVal hwnd As Long, ByVal message As Long, ByVal idTimer As Long, ByVal dwTime As Long)
On Error GoTo Err_CheckTimeOutStatus
If CBool(GetProp(GetDesktopWindow, OnTimeOutFromExternalPropName)) Then
TimedOut
Exit Function
End If
If OnTimeFlag And (Now >= TimeOutOnTime(0)) Then
TimedOut
Exit Function
End If
If OnApplicationDeactivationFlag Then
If GetForegroundWindow = AppHwnd And Application.WindowState <> xlMinimized Then
If Not OnUserIdleFlag Then TimeOutOnTime(1) = Now
Else
If HasTimedOut Then
TimedOut
Exit Function
End If
End If
End If
If OnWorkBookDeactivationFlag Then
If ThisWorkbook Is ActiveWorkbook Then
TimeOutOnTime(1) = Now
Else
If HasTimedOut Then
TimedOut
Exit Function
End If
End If
End If
If OnUserIdleFlag Then
Dim LastInput As LASTINPUTINFO
LastInput.cbSize = Len(LastInput)
If GetLastInputInfo(LastInput) <> 0 Then
If LastInput.dwTime <> LastInputTickCount Then
TimeOutOnTime(1) = Now
Else
If HasTimedOut Then
TimedOut
Exit Function
End If
End If
LastInputTickCount = LastInput.dwTime
End If
End If
Exit Function
Err_CheckTimeOutStatus:
StopOnTimePlus
Debug.Print "Unhandled error in Function CheckTimeOutStatus," & Err.Number & ", " & Err.Description
End Function
Private Function HasTimedOut() As Boolean
If DateDiff("s", TimeOutOnTime(1), Now) >= TotalSeconds Then
HasTimedOut = True
End If
End Function
Private Function TimedOut()
On Error Resume Next
StopOnTimePlus
If RunProcedureName <> "" Then
Application.OnTime Now, RunProcedureName
If Err.Number = 1004 Then
Err.Raise 10003, "Function TimedOut", Err.Description
End If
End If
ResetVariables
End Function
</textarea>