You can call the stoptimer routine in your _beforeclose procedu

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub

Then you don't need to duplicate the code (and take a chance that you don't fix
both when you have to change it).

The StopTimer routine is nice to keep separate -- especially when you're testing
and want to kill the next run.

Eric wrote:

Do I need to insert both StopTimer and Workbook_BeforeClose into worksheet in
order to make it work? but I find nothing calling StopTimer under any module.
Could you please give me more suggestions?
Thanks in advance for any suggestions
Eric

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen,Procedu=cRunWhat, _
Schedule:=False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnTime dTime, "MyMacro", , False / Error in this line

End Sub

"Dave Peterson" wrote:

If you look at Chip's site, you'll see this code:

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen,Procedu=cRunWhat, _
Schedule:=False
End Sub


The "on error resume next" line is there in case there isn't a pending ontime
macro for that time.

Eric wrote:

When I try following codes, there is error on this line.
Do you have any suggestions on how to fix it?
Thanks in advance for any suggestions
Eric

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnTime dTime, "MyMacro", , False / Error in this line

End Sub

"Dave Peterson" wrote:

Chip Pearson's site explains it:
http://www.cpearson.com/excel/OnTime.aspx

Eric wrote:

Does anyone have any suggestions on how to set macro to run every 15 minutes?
Thanks in advance for any suggestions
Eric

--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson