turning off ON_TIME

[mikerickson]

I have an ON_Time routine running. My book is in another county. How do I stop this thing from opening itself up and running. How do I stop a sub that has been set to run On_Time?

Later edit:
Cool down. I figured I could kill the reset start time part of the routine. The weirdness of a closed file opening itself threw me for a bit.

To generalize

if I have a sub

Sub mySub() 
' some code 
Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 10), procedure:="mySub" 
End Sub

What code, in a different sub will tell excel to stop running mySub every 10 seconds.

I also posted this on the other website.

[Leith Ross]

Hello Mikerickson,

I have included this portion of the VBA Help for those who may not know about OnTime.

OnTime Method Example

This example runs my_Procedure 15 seconds from now.
Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

This example runs my_Procedure at 5 P.M.
Application.OnTime TimeValue("17:00:00"), "my_Procedure"

This example cancels the OnTime setting from the previous example.
Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
    Procedure:="my_Procedure", Schedule:=False

Sincerely,
Leith Ross

[mudraker]

This example cancels the OnTime setting. Looks like you need to know the next schedule run time.

Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
    Procedure:="my_Procedure", Schedule:=False

Another option is to use a variable that is available to both macros (Boolean type)

have your cancel macro set it to true and the ontime macro test its true or false setting

Public bCancelSetOnTime As Boolean

Sub mySub()
   If bCancelSetOnTime = False Then
      Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 10), procedure:="mySub"
   End If
End Sub

Sub Macro1()
bCancelSetOnTime = True
End Sub

[mikerickson]

Thanks much.