Help : Stop Application.onTime before this workbook close

[anish.ms]

Hi

Request help in the following

I have the following code in ThisWorkbook

Private Sub Workbook_Open()
Application.Run "Calculate_Range"
End Sub


and the following code in module

Sub Calculate_Range()
ThisWorkbook.Sheets("Sheet1").Range("G3").Calculate
Application.OnTime DateAdd("s", 1, Now), "Calculate_Range"
End Sub


My problem here is when I try to close this workbook it doesn't get closed if any other workbook is open
How can I stop Application.OnTime before the this workbook close

[p45cal]

You can cancel an OnTime setting if you know what it's calling and at what time, so instead of using DateAdd("s", 1, Now) directly, put that value in a global variable and call Ontime with that variable (global variable name zzz here):
Sub Calculate_Range()
ThisWorkbook.Sheets("Sheet1").Range("G3").Calculate
zzz = DateAdd("s", 1, Now)
Application.OnTime zzz, "Calculate_Range"
End Sub

then in the before_close event handler have the line:

Application.OnTime zzz, "Calculate_Range", Schedule:=False

see https://docs.microsoft.com/en-us/office/vba/api/excel.application.ontime?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaxl10.chm133184);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

[Paul_Hossler]

Nice.

I think you need some more control flags

Standard module:



Option Explicit


Public NextRunTime As Date
Public StopRunning As Boolean


Sub Tree_On()


ThisWorkbook.Sheets("Sheet1").Calculate


NextRunTime = DateAdd("s", 1, Now)


If StopRunning Then Exit Sub

StopRunning = False
Application.OnTime NextRunTime, "Tree_On"


End Sub


'https://docs.microsoft.com/en-us/office/vba/api/excel.application.ontime
Sub Tree_Off()
StopRunning = True
On Error Resume Next
Application.OnTime NextRunTime, "Tree_On", , False
On Error GoTo 0


End Sub





Workbook module



Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Tree_Off
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Tree_Off
End Sub


Private Sub Workbook_Open()
Tree_On
End Sub

[anish.ms]

Thanks for your support always Paul_Hossler (http://www.vbaexpress.com/forum/member.php?9803-Paul_Hossler) and p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal)