timestamp now() vs live clock

[palaeontology]

In sheet 1 cell B2 I'm returning a live clock using the following code placed in Module 1 ...

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

In sheet 1 cell B1 I'm trying to return just a timestamp (also in hh:mm:ss AM/PM format) of when a Form Control Command Button is pressed. The Command Button is assigned the following macro stored in Module 2 ...

Sub startTimeinB1()

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    
End Sub

However, the time in B1 (which should not be changing as time ticks by) is changing every second, just like the live clock in cell B2.

Is there a way I can change my code(s) to prevent the time in B1 from updating ?

 

[Dan_W]

Try this instead:

Sub startTimeinB1()

    Sheet1.Range("B1").Value = Now
 
End Sub

If you want to use the same formatting, replace the line with:

Sheet1.Range("B1").Value = Format(Now, "hh:mm:ss AM/PM")

 

[palaeontology]

That worked brilliantly,


thankyou so much,

very kindest regards,

Chris