Exiting a timer macro before calling it again

Question

I have an excel countdown macro that works fine, but I need to be able to exit the routine if it's called a second time before the first version runs to zero... right now, when the user creates a transaction while the time is counting down, it starts the clock over but the first call (still counting down) does not exit and the counter resets then proceeds to count down by 2 and if that happens again, it resets and counts down by 3, so obviously the earlier calls are still running. I need a way to determine if earlier countdown routines are active and exit those before another call to it is initiated.

I was going to try to pass the current value of counter in to the routine (as a variable) then check to see if it's greater than the value running, but since that's still AM51, i didn't think that would work, or it might immediately exit any call to the routine.

Any help would be appreciated. Here's the routine (AM51 has the initial timer value which gets set by another subroutine before StartClock is called).

Sub StartClock()

Dim Clock As Date

Clock = Now + TimeValue("00:00:01")

If Range("AM51").Value = 0 Then

    Exit Sub

End If

If Range("AM51").Value < 7 Then

    Beep

End If

Range("AM51") = Range("AM51") - 1

Application.OnTime Clock, "StartClock"

End Sub

Answer 1

Application.OnTime() has Schedule as the 4th argument. If you set this to False, it cancels the timer. The tricky part is to keep track of the Clock argument that was originally used to initiate the timer. I suggest you move your Clock variable to the global scope and put the following line at the beggining of you timer function:

Application.OnTime EarliestTime:=Clock, Procedure:="StartClock", Schedule:=False