Need to watch the seconds to make sure the clock is running correctly.
That is a mistake. For one thing the scheduled run time is actually the earliest run time. It can run any time after that. This timing error adds up to possible several minutes of error over a day's run. For more info, refer to offthelip's posts.
Try this in a Standard Module in Workbook2
Dim CurrentCell As Range
Dim Initalised As Boolean
Dim SchedRecalc as Date
Private Sub Recalc()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
With Sheets("Clock") 'Edit to suit
CurrentCell = Date
CurrentCell.Offset(,1) = Time
Set CurrentCell = CurrentCell.Offset(1)
' Stop repeating after 24 hours (1440 minutes)
If CurrentCell.Row> 1440 Then Initalised = False
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
SetTime
End Sub
'-----------------------------------------
Sub SetTime()
If (Not Initalised) Then
Initalised = True
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
' Initialise variables.
Set CurrentCell = Sheets("Clock").Range("C1")
SchedRecalc = Date + Format(Now, "hh:mm:00") 'Every minute at the minute
Sheets("Clock").Range("C1:C1440").ClearContents
CurrentCell = Date
CurrentCell.Offset(,1) = Time
Set CurrentCell = CurrentCell.Offset(1)
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End If
SchedRecalc = SchedRecalc + TimeValue("00:01:0") 'Every minute at the minute
Application.OnTime SchedRecalc, "Recalc"
End Sub