#1   Report Post  
Posted to microsoft.public.excel.misc
Antonio
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Chip Pearson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Chip Pearson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Antonio
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Antonio
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula to determine time range overlap? William DeLeo Excel Discussion (Misc queries) 0 June 6th 06 08:26 PM
Hot key for time? Dave in Des Moines Excel Worksheet Functions 2 March 24th 06 05:15 PM
time sheet drop down lists Steve Excel Discussion (Misc queries) 12 March 18th 06 11:30 PM
Entering Times Denise Excel Discussion (Misc queries) 9 November 15th 05 05:57 PM
time differences in a column 68magnolia71 Excel Worksheet Functions 3 May 9th 05 09:46 PM


All times are GMT +1. The time now is 11:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"