Can't get application to "time out" correctly

[RSW]

I posted this in the Excel forum, but didn't receive any responses. Is this forum for Excel VBA as well as Access?




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/...d.php?t=194313

Thread to:

http://www.access-programmers.co.uk/...d.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



Code:
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 SubIt 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!!

 

[darbid]

Hi - I think you are in the wrong forum but I will get you on the right track. I do not play with excel but have a look here http://msdn.microsoft.com/en-us/library/aa195809(office.11).aspx

see the last comment there. It shows how to cancel the timer. So what you need to do is put that in here first to cancel the timer and then set another one.

 

[RSW]

Thank you! I will give that a try.