On time kill

[Antonio]

I have the following code that runs every minute

Application.OnTime Now + TimeValue("00:01:00"), "max_min"

I want to cancel that OnTime schedule in the Before_Close event.

How can I do that. I am having difficulties with the EarliestTime

Application.OnTime EarliestTime:=Now+TimeValue("00:01:00"),
Procedure:="max_min", Schedule:=False

does not work

If I don't kill the call to max_min the spreadsheet keeps opening up or
causing errors when it is opened.

Thanks,

Antonio

 

[Chip Pearson]

To cancel an OnTime event, you must provide the EXACT time that
it is scheduled to run. Therefore, you should store that value in
a public variable, and pass that variable to OnTime. E.g.,

Public RunWhen As Double
Sub StartOnTime()
RunWhen = Now + TimeValue("00:01:00")
Application.OnTime RunWhen, "max_min"
End Sub

Sub EndOnTime()
Application.OnTime RunWhen, , , False
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

Application.OnTime RunWhen, , , False
should be
Application.OnTime RunWhen, "max_min", , False


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Antonio]

Hi Chip,

I tried something similar but would not work.

The following does not work, the compiler does not like it:

Application.OnTime EarliestTime:=runwhen, Procedure:="max_min",
Schedule:=False


The following is accepted by the compiler but does not kill the OnTime call.
It is executed still:

Application.OnTime runwhen, "max_min", , False

I have even put the runwhen value in a cell and copy from it. (the runwhen
was not passed from one module to the other although it was declared as
Public), no luck.

Also, I do not understand why the first syntax is not correct

What am I missing?

Thank you very much again, much appreciated.

Antonio

 

[Antonio]

Hi Chip,

I made it work.

I had to put a procedure to kill the OnTime inside the same module.

I did not know that Public variables are not passed from a module to the
ThisWorkbook object.

I thought otherwise after reading in the help files:
"Variables declared using the Public statement are available to all
procedures in all modules in all applications unless Option Private Module is
in effect; in which case, the variables are public only within the project in
which they reside."

Can you explain why it is not passed to the ThisWoorkbook object?

Thanks,

Antonio