OnTime code or program stop

[crashcb18]

Hello all, first time posting to a board in a while.. first time posting to a software programming board so please bear with me.

Excel 2011 (Mac), will eventually use on a Windows Excel 2010.

I'm trying to put a running clock into my excel file that updates once a minute, or whatever time I specify. I just started learning VBA a week ago, my background is with Engineering Matlab programming. I searched for code online to start with. I have the time updating feature working fine and can adjust the time delay. My issue is executing the "StopClock()" procedure with the "Schedule:=False" statement to stop the code. Even though the few posts I've searched and websites say to use this method to stop the OnTime procedure, but its not stopping the code and its still running when I execute or "run" the "StopClock" code. Please advise... code below.

Also, why does this UpdateClock routine only work in a "Module" and not in "ThisWorkbook" or "Sheet5" programming windows? When I try it in those windows, Excel says it can't find the "UpdateClock" code. This question is education to me of how Excel looks for its programs or routines or procedures. I apologize, I'm still getting the terminology, and yes I do have a 1000 page book on excel and downloaded another book on Excel VBA.

Also, how do I break out my code into a separate in-window window for these posts?

---------------------
Sub UpdateClock()
Dim NextTick As Date
'Updates cell A2 with the current time

'ThisWorkbook.Sheets("Sheet5").Range("A2") = Format(Time(), "hh:mm:ss")
Sheets("Sheet5").Range("A2") = Format(Time(), "hh:mm:ss")
' Set up the next event five seconds from now
NextTick = Now() + TimeValue("00:00:15")
MsgBox "The time has now been updated to " & NextTick
Application.OnTime NextTick, "UpdateClock", Schedule:=True
End Sub

Sub StopClock()
'Cancels the OnTime event (stops the clock)
On Error Resume Next
Application.OnTime Now, "UpdateClock", Schedule:=False
End Sub
------------------

 

[AlphaFrog]

Hi and welcome.

My issue is executing the "StopClock()" procedure with the "Schedule:=False" statement to stop the code. Even though the few posts I've searched and websites say to use this method to stop the OnTime procedure, but its not stopping the code and its still running when I execute or "run" the "StopClock" code.

The stopclock procedure has to use the same time value as the last Updateclock ontime event. Declaring the NextTick variable at the top of the module makes it accessible to each procedure.

[COLOR="Red"]Public NextTick As Date[/COLOR]

Sub UpdateClock()
    'Updates cell A2 with the current time

    'ThisWorkbook.Sheets("Sheet5").Range("A2") = Format(Time(), "hh:mm:ss")
    Sheets("Sheet5").Range("A2") = Format(Time(), "hh:mm:ss")
    ' Set up the next event five seconds from now
    NextTick = Now() + TimeValue("00:00:15")
    MsgBox "The time has now been updated to " & NextTick
    Application.OnTime [COLOR="Red"]NextTick[/COLOR], "UpdateClock", Schedule:=True
End Sub

Sub StopClock()
    'Cancels the OnTime event (stops the clock)
    On Error Resume Next
    Application.OnTime [COLOR="red"]NextTick[/COLOR], "UpdateClock", Schedule:=False
End Sub

Also, why does this UpdateClock routine only work in a "Module" and not in "ThisWorkbook" or "Sheet5" programming windows?

I don't know why it is the way it is.

Also, how do I break out my code into a separate in-window window for these posts?

See the signature block below.
Additional BB Codes