Stop OnTime event

[MrKowz]

Hey all,

I am helping another user with a procedure that uses an Application.OnTime event to have the code run every minute. However, I need to have a way to stop that event manually. Basically, there would be two buttons, a "Stop" and an "End".

I thought to make a separate procedure that changes the scheduling to false, but I get the error "Method 'OnTime' of object '_Application' failed.

Here are the two codes - any ideas?

Macro to run at start:

Public Sub GolferTeeTime()
Dim x           As Variant, _
    temptime    As String, _
    rng         As Range, _
    rng1        As String, _
    counter     As Long, _
    LR          As Long
 
counter = 2
Range("D1").Value = Format(Now, "h:mm am/pm")
temptime = Format(Now - TimeValue("00:02:00"), "h:mmam/pm")
x = Application.Match(temptime, Range("A:A"), 0)
If Not IsError(x) Then
    Range("D2:D10").ClearContents
    With Range("A:A")
        Set rng = .Find(temptime, LookIn:=xlValues)
        If Not rng Is Nothing Then
            rng1 = rng.Address
            Do
                Range("D" & counter).Value = rng.Offset(0, 1).Value
                counter = counter + 1
                Set rng = .FindNext(rng)
            Loop While Not rng Is Nothing And rng.Address <> rng1
        End If
    End With
End If
Application.OnTime Now + TimeValue("00:01:00"), "GolferTeeTime", , True
End Sub

Macro to Stop the above OnTime event (this one errors):

Public Sub StopGolferTeeTime()
    [COLOR=red][B]Application.OnTime Now, "GolferTeeTime", , False[/B][/COLOR]
End Sub

 

[VoG]

Hi. Try like this

Public dTime As Date

Public Sub GolferTeeTime()
Dim x           As Variant, _
    temptime    As String, _
    rng         As Range, _
    rng1        As String, _
    counter     As Long, _
    LR          As Long
 
counter = 2
Range("D1").Value = Format(Now, "h:mm am/pm")
temptime = Format(Now - TimeValue("00:02:00"), "h:mmam/pm")
x = Application.Match(temptime, Range("A:A"), 0)
If Not IsError(x) Then
    Range("D2:D10").ClearContents
    With Range("A:A")
        Set rng = .Find(temptime, LookIn:=xlValues)
        If Not rng Is Nothing Then
            rng1 = rng.Address
            Do
                Range("D" & counter).Value = rng.Offset(0, 1).Value
                counter = counter + 1
                Set rng = .FindNext(rng)
            Loop While Not rng Is Nothing And rng.Address <> rng1
        End If
    End With
End If
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "GolferTeeTime", , True
End Sub

Public Sub StopGolferTeeTime()
    Application.OnTime dTime, "GolferTeeTime", , False
End Sub

 

[MrKowz]

Thanks much, VoG. That makes perfect sense once it is put like that.