Wierd one concerning Before_Close and OnTime

[Antonio]

The following kills the OnTime max_min when the workbook is closed normally

Public Sub Workbook_BeforeClose(cancel As Boolean)

kill_max_min

End Sub



Public Sub kill_max_min()

Application.OnTime TimeValue(Hour(Now) & ":" & Minute(Now)) +
TimeValue("00:01"), "max_min", , False


End Sub

However, when the workbook is closed from a different module with the
Workbook. Close method, the BeforeClose above seems to run well and runs the
kill_max_min but the OnTime is not killed, it comes back up as scheduled.



Why is that?

Thanks,

Antonio

 

[Ardus Petus]

You could maintain a global variable that holds the timer value.

Please post your code.

HTH

 

[Antonio]

Hi Ardus,

Excuse the simple question, how do I maintain a global variable?

I have used a Public one to no avail.

Thanks,

Antonio

 

[Antonio]

Hi again,

Concerning your last comment. Do you want me to post the whole code?

Thanks,

Antonio

 

[Ardus Petus]

Here's an example (whole module):

'-------------------------------------------------
Option Explicit

Dim dNext As Date

Sub Autpen()
dNext = TimeSerial(Hour(Now) + IIf(Minute(Now) < 18, 0, 1), 18, 0)
Application.OnTime dNext, "refreshdata"
End Sub

Sub refreshdata()
MsgBox Now
dNext = TimeSerial(Hour(dNext) + 1, 18, 0)
Application.OnTime dNext, "refreshdata"
End Sub

Sub cancelTimer()
Application.OnTime dNext, "refreshdata", , False
End Sub
'--------------------------------------------------------

HTH

 

[Antonio]

Hi Ardus,

Yes, I understand your code. refreshdata is run every 18'' past the hour.

That is equivalent to what I was doing.

However, it does not answer the original question. If you call the
cancelTimer from the BeforeClose method it won't execute

 

[Ardus Petus]

Yes it will!

--
AP

Hi Ardus,

Yes, I understand your code. refreshdata is run every 18'' past the hour.

That is equivalent to what I was doing.

However, it does not answer the original question. If you call the
cancelTimer from the BeforeClose method it won't execute

 

[Dave Peterson]

You have a follow up question at your previous post.

 

[Antonio]

Sorry Dave, how do I find my posts? I don't find the way to do it and I may
have missed the previous post. Thanks, Antonio

 

[Antonio]

How weird that it works for you. I have not tried your actual code though.

How can I post the whole code in a practical readable manner?

 

[Antonio]

Sorry Ardus, I meant, when closing the workbook from another workbook with
the workbook.close method the before close seems to exectute the canceltimer
but it is not killed, it comes back.

 

[Dave Peterson]

use this
http://groups.google.com/advanced_group_search

to search for your posts.

My question (repeated here):

Why do you say that the workbook_beforeclose event doesn't fire when closing
Workbook A from workbook B?

It does for me.

In fact, the only way I know to stop it is to disableevents before I close that
workbook.

If you're doing that, is there a reason?

 

[Ardus Petus]

You can upload your workbook on http://cjoint.com/ and post back the
generated link.
(the site is in french, but it's straightforward)

HTH

 

[Antonio]

Hello Dave,

Before I continue, let me express my gratitude once again. Much appreciated.

I managed to find your post and I replied to it. Yes, it fires, but the
ontime is not killed. I have tried it again several times.

I know now part of the explanation of my other problems.

It turns out that if excel is running in the background sometimes the timer
is not refreshed, then there is a mismatch between the timing as per the
formula of exact minutes next and the last ontime scheduled.

I have solved it by the crude way of storing in a cell the time of the next
ontime everytime a refresh happens.

I retrieve the content of that cell before the cancel Ontime and use it as
the earliest time parameters.

Is there a better way to store a global variant across projects?

 

[Dave Peterson]

You could use a public variable in the workbook that's being closed.

I think you should take a look at how Chip Pearson does it:
http://www.cpearson.com/excel/ontime.htm

Notice that when he stops the process, it's using that global variable.

Hello Dave,

Before I continue, let me express my gratitude once again. Much appreciated.

I managed to find your post and I replied to it. Yes, it fires, but the
ontime is not killed. I have tried it again several times.

I know now part of the explanation of my other problems.

It turns out that if excel is running in the background sometimes the timer
is not refreshed, then there is a mismatch between the timing as per the
formula of exact minutes next and the last ontime scheduled.

I have solved it by the crude way of storing in a cell the time of the next
ontime everytime a refresh happens.

I retrieve the content of that cell before the cancel Ontime and use it as
the earliest time parameters.

Is there a better way to store a global variant across projects?