Simple VBA running clock macro that I can't seem to stop without forcing an error

  • Hello all,

    Windows 10 Pro, MS Office 2010, Excel.

    I have a simple VBA macro that starts a running clock in a cell. Below is the code that works to insert a running clock in "My_ClockSheet1, Cell A1" that refreshes every second.

    Code
    Sub UpDateClock()
    ' *** Change Sheet name and Range reference to suit ***
    Worksheets("My_ClockSheet1").Range("A1").Calculate
    Application.OnTime Now + TimeValue("00:00:01"), "UpdateClock"
    End Sub

    It won't stop unless I open a seperate workbook, forcing a Script out of range error due it seems to the opening workbook not having a sheet of the same name. I hit End in the error window, the clock stops and I can save and close the workbook as normal. Seems like it should be easy but everything I've tried doesn't seem to work. When saving the document and closing, it just pops back open. When I try to execute an Esc key hit after saving and closing, it's hit or miss if the timing is right, more miss than hit.

    It is easy to put a button pointing to UpDateClock sub or even a call from another macro. I want a clickable button to stop the clock whenever needed.

    Thanks in Advance for any help,

    Bud,

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!