More problems setting up a "time out"

[RSW]

This is the third in a series of threads...I'm way over my head on this, and seem to be wasting countless hours.

Thread one:

http://www.access-programmers.co.uk/forums/showthread.php?t=194313

Thread to:

http://www.access-programmers.co.uk/forums/showthread.php?t=196358

Helpful users have been contributing great ideas, but I seem to be unable to put them all together.

I need a timer based on when the last keystroke or selection was, that will close the file after a specified period of inactivity. Taking ideas from the two threads above and elsewhere, I came up with

Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim AlertTime As Date
AlertTime = Now + TimeValue("0:00:05")
MsgBox "should close at"
MsgBox (AlertTime)

Application.OnTime AlertTime, "CloseBook"

End Sub

It should be clear what I'm trying to do here--go five seconds (for testing purposes) without changing the selection, and the CloseBook macro (which works) is called.

But this closes the workbook after five seconds, no matter how many times I change the selection.

I thought (well, hoped) that resetting the AlertTime variable would update the Application.OnTime. It clearly didn't--is there any way I can "take it back" and start anew each time?

Or am I doing this completely wrong and is there a better solution?


Thanks!!

 

[Brianwarnock]

Ah! interesting, never done this before but the solution is to unset the previous ontime, however you have to have set one to unset one therefore declare your Alertime as Public and check for a value. Set it to 0 on workbook_open. See code.

Brian

Public alerttime As Date

Private Sub Workbook_Open()
alerttime = TimeValue("00:00:00")

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If (alerttime) <> TimeValue("00:00:00") Then
Application.OnTime EarliestTime:=alerttime, _
    Procedure:="CloseBook", Schedule:=False
End If

alerttime = Now + TimeValue("0:00:10")
MsgBox "should close at  "  &  (alerttime)

Application.OnTime alerttime, "CloseBook"

End Sub

BTW You do realise that if the user does not click OK to the message the workbook will not close?