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.