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
"Antonio" wrote:
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, Procedu="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
"Chip Pearson" wrote:
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
"Chip Pearson" wrote in message
...
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
"Antonio" wrote in message
...
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"),
Procedu="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