Create Live Clock in Excel (Visual Basic for Applications)

Live Clock

Looking for a working solution where you need a running time in your Dashboard? In this article we will look how we can create a live clock which ticks like Digital Clock.

Step 1: Create design as per your wish but need two buttons. I have used shapes and changed the labels as START and STOP respectively.

Step 2: Choose the cell in which you wish to show your clock and give it a name, I have used “otime”. Select Formulas tab click on Name Manager button to add a Name (Need more details adding a name please refer my previous post “Name in Excel“

Step 3: Format the cell which you need to make as clock “otime” by pressing CTRL + 1 or right click on the cell and choose Format Cells… then select Time from Number tab under Format Cells dialog as shown below and choose desire format of your clock:

Step 4: Insert a standard module where we will write code to make our clock live. Put following code in General section:

Dim SchedRecalc As Date

Create Start Clock procedure

Public Sub ClockStart()

    With Range("otime")
    
        .Value = Format(Time, "hh:mm:ss")
    
    End With
    'Call timer
    Call SetTime

End Sub

Put Set timer code as below

Sub SetTime()

    SchedRecalc = Now + TimeValue("00:00:01")
    
    Application.OnTime SchedRecalc, "ClockStart"

End Sub

STOP or DISABLE the watch

Public Sub Disable()

    On Error Resume Next
    
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="ClockStart", Schedule:=False

End Sub

Step 5: Navigate back to Excel sheet and select START button. Right Click and choose Assign Macro from the list.

Select ClockStart from the given dialog and select OK:

Repeat the same for STOP button. now click somewhere on the sheet and Click on START button. Hurra………..!!!!!

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Share on Facebook

Tweet