Ending a Sub on a Timer Loop

[kfkramer1]

Hey All!

I could use your help and expertise on this one. I am trying to end a Sub in a Macro that is on a timer loop. Please see the following Module.



Sub my_Procedure()
MsgBox "hello world"
Call test ' for starting timer again
End Sub

Sub test()
Application.OnTime Now + TimeValue("00:00:30"), "my_Procedure"
End Sub



How do I stop the "my_Procedure" subroutine from repeating?

[Logit]

Either delete this line from my_Procedure :
Call test ' for starting timer again

Or you can comment out the line by placing a single quotation mark in front of the code line :
' Call test ' for starting timer again

[kfkramer1]

I guess I should have stated:

How do I create a Sub that will stop this repeating loop?

I tried:

Sub Box()
MsgBox "hello world"
Call test ' for starting timer again
End Sub


Sub test()
Application.OnTime Now + TimeValue("00:00:30"), "Box"
End Sub


Sub StopMacro()
End
End Sub

------------

Nothing worked on this....the box keeps popping up. I thought "End" stopped all Macros?

[Logit]

Paste into a routine module:



Option Explicit
Dim NextTick As Date, t As Date


Sub StartStopWatch() '__________________________________________________________________________ ____'same as 'Box' in your code
MsgBox "hello world"
t = Time
Call StartTimer
End Sub


Sub StartTimer()' ___________________________________________________________________________ _____ 'same as 'test' in your code
NextTick = Time + TimeValue("00:00:30") '_______________________________________________________'updates time display A1 every 30 seconds
Range("A1").Value = Format(NextTick - t - TimeValue("00:00:01"), "hh:mm:ss") '__________________'keeps track of time passing each second
Application.OnTime NextTick, "StartTimer" '_____________________________________________________'re-iterates itself to count every second
End Sub

Sub StopTimer() '__________________________________________________________________________ _________'same as 'StopMacro' in your code
On Error Resume Next
Application.OnTime EarliestTime:=NextTick, Procedure:="StartTimer", Schedule:=False
End Sub





Two command buttons on Sheet1 : one attached to macro StartStopWatch ... the other attached to macro StopTimer