Live Clock in a single cell

[palaeontology]

I am trying to get a live clock appearing in cell B2 of Sheet 1.

This is what I have as my Module 1, but it isn't working ... I need it to activate the moment the workbook is opened.

Dim SchedRecalc As Date

Sub Recalc()

With Sheet1.Range("B2")

.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

 

[DanteAmor]

Try the following:

In the events of thisworkbook put this:

Private Sub Workbook_Open()
  Call Recalc
End Sub

ThisWorkbook EVENT
- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the above code.


In a module:

Dim SchedRecalc As Date

Sub Recalc()
  Application.Volatile
  With Sheet1.Range("B2")
    .Value = Format(Time, "hh:mm:ss AM/PM")
  End With
  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

On the sheet you should have a button that runs the Disable macro to stop the clock.