OnTime method
 

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

OnTime method
 

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.

In article ,
xlcharlie wrote:

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