Stamp Date

[Malcolm torishi]

I have the following vba time stamp code in a module that does not quite do what I want it to do. I would like the clock to start automatically rather than having to go into the marco and run “Recalc”. Also I would like to close the spread sheet in a normal way rather than when i close the spread sheet the spread sheet closes for a split second and then reopens because the clock is still in “Recalc”
Can any one suggest what extra code I would need to add to the following
thanks

Dim SchedRecalc As Date
Sub Recalc()
With Sheet1.Range("G1")
.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

 

[John_w]

Call Recalc from Workbook_Open and call StopTimer from Workbook_Close, both in the ThisWorkbook module.

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=SchedRecalc,Procedure:="Recalc", Schedule:=False
End Sub

 

[Malcolm torishi]

Hi thank you for coming back, I have tried this out today but couldn’t get it to work. I have obviously inserted the code into the wrong place . Any chance you could explain more please.
thank you

 

[John_w]

In the ThisWorkbook module:

Private Sub Workbook_Open()
    Recalc
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

See https://www.contextures.com/xlvba01.html#Workbook

 

[Malcolm torishi]

Hello again
For some strange reason I can only get the beginning of this code to work. Eg the open Recalc. The stop timer section of the code locks up and I get a yellow highlighted line over the private sub line line in the code. It works great when the spread sheet opens, the clock is ticking but it just will not allow me to close and save the spreadsheet without going into this yellow highlighted line over the private sub line. If you can help thank you , otherwise I will have another go tomorrow, thank you agian