OnTime method

[xlcharlie]

I'm trying to use the OnTime method to continually execute a macro every so
often. The problem I am having now is that after I close out of the
workbook, the OnTime method executes the specified procedure after a period
of time and Excel automatically opens the file back up. I've tried using a
BeforeClose event along with another OnTime method to clear the procedure,
but it's giving me a run-time error saying "Method 'OnTime" of object
'_Application' failed.

Is there another method I can use to cycle through the procedure
periodically that will not execute once I've close the workbook? Or is there
a way to clear the procedure using a BeforeClose event? Perhaps there may
even be a way to correct the code I am using that is giving me the error.
Code below appearing in the ThisWorkbook object. Thanks.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime Now + TimeValue("00:00:01"), "killTimer", , False
End Sub

Sub killTimer()
ThisWorkbook.Save
End Sub

 

[JE McGimpsey]

You need to kill the scheduled OnTime execution, which means you need to
use the exact time that your routine is scheduled.

See

http://cpearson.com/excel/ontime.htm

for a way to do it using a global variable to store the next time to
execute.