Stop timed macro on close

I have the following macro to recalculate the time every second on opening a spreadsheet

Sub Auto_Open()
Calculate
Application.OnTime Now + TimeValue("00:00:01"), "Auto_open"
End Sub

Looking for the code I need to add that will stop the macro when the file is closed.

Many thanks

From Help for OnTime:

This example cancels the OnTime setting from the previous example.

Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
    Procedure:="my_Procedure", Schedule:=False

Please read the Forum Rules and then edit your post to wrap your code with Code Tags.

I am a novice, and this took me quite a while to do....do I simply add your code? I simply (or not so simply) want the spreadsheet to stop the macro and close when the file closes

Sub Auto_Open()

   Calculate
   
    Application.OnTime Now + TimeValue("00:00:01"), "Auto_open"
    Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
    Procedure:="my_Procedure", Schedule:=False
End Sub

Thanks

Allardin,

Please read the Forum Rules and then edit your post to wrap your code with Code Tags, and I'll help you.

Thank you.

Put this in the ThisWorkbook module:

Private Sub Workbook_Open()
    CalculateMe
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime datNextCalc, "CalculateMe", , False
End Sub

Put this in a code module:

Public datNextCalc As Date

Sub CalculateMe()
    Calculate
    datNextCalc = Now + TimeValue("00:00:01")
    Application.OnTime datNextCalc, "CalculateMe"
End Sub