Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
On time kill
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
On time kill
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
On time kill
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
On time kill
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
On time kill
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to determine time range overlap? | Excel Discussion (Misc queries) | |||
Hot key for time? | Excel Worksheet Functions | |||
time sheet drop down lists | Excel Discussion (Misc queries) | |||
Entering Times | Excel Discussion (Misc queries) | |||
time differences in a column | Excel Worksheet Functions |