CLOCK WITH AUTOMATIC REFRESH

CLOCK WITH AUTOMATIC REFRESH

Use & Explanation:

The steps below will show you how to make a clock that automatically refreshes without having to use the F9 key.

STEPS IN DETAIL

STEP 1

• On a New Excel Sheet, go to Tools, then go to Macro, then go to Visual Basic Editor. (Or you can also simply skip the step above and press the Alt key with the F11 key to open up the Visual Basic Editor)

STEP 2

• In the new screen that you see, Right click on the Sheet1, click on Insert, then click on Module.

STEP 3

• In Module1, copy paste the code given below, into the blank screen on the right side of the screen, just as shown in the snapshot.

• Now click on Sheet1 on the left side of the screen.

• Copy paste the code given below, here aswell.

• Save the sheet and close the entire sheet completely.

Note:You can change the cell no. highlighted in red, depending on where you want to put the the clock in your excel sheet.

Dim SchedRecalc As Date

Sub Recalc()

With Sheet1.Range("A1")

.Value = Format(Time, "hh:mm:ss AM/PM")

End With

Call SetTime

End Sub

Sub SetTime()

SchedRecalc = Now + TimeValue("00:00:01")

Application.OnTime SchedRecalc, "Recalc"

End Sub

Sub Disable()

On Error Resume Next

Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False

End Sub

STEP 4

• Open your Excel sheet once again, Click on Enable Macros, if prompted.

• Next go to Tools, then go to Macro, then go to Macros. (Or you can also simply skip this step and press the Alt key with the F8 key to open up Macros)

STEP 5

• In the Macros window, select Set Time.

• Click on Options.

• You can set any alphabet of your choice as a short cut key to automatically start the clock.

• Click OK, and then click Run.

STEP 6

• Format the clock as you like, or as shown.

STEP 7

• If you want the date as well, this will refresh every time you open the sheet, do the following:

• In Cell A2 copy paste this formula =today() as shown below.

STEP 8

• If you want to have a graphical clock (like the one shown below) that keeps moving every second, then copy paste these 3 Formulas into the cells as shown in the snapshot below:

• In Cell B3 copy paste the formula :

=REPT("|",HOUR(NOW()))&" "&TEXT(HOUR(NOW()),"00")

• In Cell B4 copy paste the formula :

=REPT("|",MINUTE(NOW()))&" "&TEXT(MINUTE(NOW()),"00")

• In Cell B5 copy paste the formula :

=REPT("|",SECOND(NOW()))&" "&TEXT(SECOND(NOW()),"00")

The completed Clocks and Date will look like this: