Loop with Time

[portucale]

Hi,
New to this programming hence I am looking for some help.
I need to write a macro/vba in Excel which will open diferent workbooks according a specific time, i.e. Book 1 (open) then open Book 2 after 2 minutes, Book three after 4 minutes of the first (2 minutes of teh second).

Your help will be much appreciated.

Thanks,

[excelent]

Maby u can use this as inspiration

http://pmexcelent.dk/FileMaster.xls

[Antonio]

Hi Excelent,

I have been looking at your code.

Since you have worked with the Application On Time command I wonder if you
have run into the following difficulty:

If you try to close the workbook that has the timer running from another
workbook, programatically, the Before_Close event will run the Stoptimer but
it won't kill it. It will refire again.

This has caused me ample problems.

Try it
"excelent" wrote:

> Maby u can use this as inspiration
>
> http://pmexcelent.dk/FileMaster.xls
>

[Bob Phillips]

You should kill the timer. Chip Pearson explains it all on
http://www.cpearson.com/excel/ontime.htm

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Antonio" <[email protected]> wrote in message
news:[email protected]...
> Hi Excelent,
>
> I have been looking at your code.
>
> Since you have worked with the Application On Time command I wonder if you
> have run into the following difficulty:
>
> If you try to close the workbook that has the timer running from another
> workbook, programatically, the Before_Close event will run the Stoptimer
but
> it won't kill it. It will refire again.
>
> This has caused me ample problems.
>
> Try it
> "excelent" wrote:
>
> > Maby u can use this as inspiration
> >
> > http://pmexcelent.dk/FileMaster.xls
> >

[excelent]

nope cant say i have, i made this sheet for another, he newer told me of any
problems. Anyway i use this code to stop timer

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTimer
End Sub



"Antonio" skrev:

> Hi Excelent,
>
> I have been looking at your code.
>
> Since you have worked with the Application On Time command I wonder if you
> have run into the following difficulty:
>
> If you try to close the workbook that has the timer running from another
> workbook, programatically, the Before_Close event will run the Stoptimer but
> it won't kill it. It will refire again.
>
> This has caused me ample problems.
>
> Try it
> "excelent" wrote:
>
> > Maby u can use this as inspiration
> >
> > http://pmexcelent.dk/FileMaster.xls
> >

[Antonio]

Hi Bob,

I have done intensive work on this issue.

I know how it is suposed to be done but it just does not work. It is a bug,
an annoying one.

Again, try closing a workbook with a live On Time procedure from a different
workbook, if you rely on the Before_Close event it does not work. It never
works.

You have to call the kill timer sub independently and it does not always
work.

"Bob Phillips" wrote:

> You should kill the timer. Chip Pearson explains it all on
> http://www.cpearson.com/excel/ontime.htm
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Antonio" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Excelent,
> >
> > I have been looking at your code.
> >
> > Since you have worked with the Application On Time command I wonder if you
> > have run into the following difficulty:
> >
> > If you try to close the workbook that has the timer running from another
> > workbook, programatically, the Before_Close event will run the Stoptimer
> but
> > it won't kill it. It will refire again.
> >
> > This has caused me ample problems.
> >
> > Try it
> > "excelent" wrote:
> >
> > > Maby u can use this as inspiration
> > >
> > > http://pmexcelent.dk/FileMaster.xls
> > >
>
>
>

[Bob Phillips]

As long as you call it with exactly the same scheduled time as when you set
it, it works fine.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Antonio" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> I have done intensive work on this issue.
>
> I know how it is suposed to be done but it just does not work. It is a
bug,
> an annoying one.
>
> Again, try closing a workbook with a live On Time procedure from a
different
> workbook, if you rely on the Before_Close event it does not work. It never
> works.
>
> You have to call the kill timer sub independently and it does not always
> work.
>
> "Bob Phillips" wrote:
>
> > You should kill the timer. Chip Pearson explains it all on
> > http://www.cpearson.com/excel/ontime.htm
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Antonio" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Excelent,
> > >
> > > I have been looking at your code.
> > >
> > > Since you have worked with the Application On Time command I wonder if
you
> > > have run into the following difficulty:
> > >
> > > If you try to close the workbook that has the timer running from
another
> > > workbook, programatically, the Before_Close event will run the
Stoptimer
> > but
> > > it won't kill it. It will refire again.
> > >
> > > This has caused me ample problems.
> > >
> > > Try it
> > > "excelent" wrote:
> > >
> > > > Maby u can use this as inspiration
> > > >
> > > > http://pmexcelent.dk/FileMaster.xls
> > > >
> >
> >
> >

[Antonio]

I wish I were wrong, I really wish.

Try this:

tta.xls Module1

Sub main()

Workbooks("ttb.xls").Close SaveChanges:=False

End Sub


ttb.xls ThisWorkbook

Public Sub Workbook_Open()

timer

End Sub

Public Sub Workbook_BeforeClose(cancel As Boolean)

cancel_timer


End Sub

ttb.xls Module1

Public rt As Double

Public Sub timer()

rt = Now + TimeValue("00:00:02")

Application.OnTime rt, "timer"

End Sub

Public Sub cancel_timer()


Application.OnTime rt, "timer", , False


End Sub

Do this now:

Open ttb.xls
Open tta.xls
Assing sub main() in tta.xls to a button, click on the button, it does close
ttb.xls but....ttb.xls reopens. This is not supposed to happen and it creates
all sorts of problems. If you know that I am wrong please correct me, you
would help me tremendously.

There is a workaround, but it is not reliable.






"Bob Phillips" wrote:

> As long as you call it with exactly the same scheduled time as when you set
> it, it works fine.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Antonio" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob,
> >
> > I have done intensive work on this issue.
> >
> > I know how it is suposed to be done but it just does not work. It is a
> bug,
> > an annoying one.
> >
> > Again, try closing a workbook with a live On Time procedure from a
> different
> > workbook, if you rely on the Before_Close event it does not work. It never
> > works.
> >
> > You have to call the kill timer sub independently and it does not always
> > work.
> >
> > "Bob Phillips" wrote:
> >
> > > You should kill the timer. Chip Pearson explains it all on
> > > http://www.cpearson.com/excel/ontime.htm
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "Antonio" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Excelent,
> > > >
> > > > I have been looking at your code.
> > > >
> > > > Since you have worked with the Application On Time command I wonder if
> you
> > > > have run into the following difficulty:
> > > >
> > > > If you try to close the workbook that has the timer running from
> another
> > > > workbook, programatically, the Before_Close event will run the
> Stoptimer
> > > but
> > > > it won't kill it. It will refire again.
> > > >
> > > > This has caused me ample problems.
> > > >
> > > > Try it
> > > > "excelent" wrote:
> > > >
> > > > > Maby u can use this as inspiration
> > > > >
> > > > > http://pmexcelent.dk/FileMaster.xls
> > > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

Well I have just tried it and it works fine for me. File B does not re-open
at all.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Antonio" <[email protected]> wrote in message
news:[email protected]...
> I wish I were wrong, I really wish.
>
> Try this:
>
> tta.xls Module1
>
> Sub main()
>
> Workbooks("ttb.xls").Close SaveChanges:=False
>
> End Sub
>
>
> ttb.xls ThisWorkbook
>
> Public Sub Workbook_Open()
>
> timer
>
> End Sub
>
> Public Sub Workbook_BeforeClose(cancel As Boolean)
>
> cancel_timer
>
>
> End Sub
>
> ttb.xls Module1
>
> Public rt As Double
>
> Public Sub timer()
>
> rt = Now + TimeValue("00:00:02")
>
> Application.OnTime rt, "timer"
>
> End Sub
>
> Public Sub cancel_timer()
>
>
> Application.OnTime rt, "timer", , False
>
>
> End Sub
>
> Do this now:
>
> Open ttb.xls
> Open tta.xls
> Assing sub main() in tta.xls to a button, click on the button, it does
close
> ttb.xls but....ttb.xls reopens. This is not supposed to happen and it
creates
> all sorts of problems. If you know that I am wrong please correct me, you
> would help me tremendously.
>
> There is a workaround, but it is not reliable.
>
>
>
>
>
>
> "Bob Phillips" wrote:
>
> > As long as you call it with exactly the same scheduled time as when you
set
> > it, it works fine.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Antonio" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob,
> > >
> > > I have done intensive work on this issue.
> > >
> > > I know how it is suposed to be done but it just does not work. It is a
> > bug,
> > > an annoying one.
> > >
> > > Again, try closing a workbook with a live On Time procedure from a
> > different
> > > workbook, if you rely on the Before_Close event it does not work. It
never
> > > works.
> > >
> > > You have to call the kill timer sub independently and it does not
always
> > > work.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > You should kill the timer. Chip Pearson explains it all on
> > > > http://www.cpearson.com/excel/ontime.htm
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "Antonio" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Excelent,
> > > > >
> > > > > I have been looking at your code.
> > > > >
> > > > > Since you have worked with the Application On Time command I
wonder if
> > you
> > > > > have run into the following difficulty:
> > > > >
> > > > > If you try to close the workbook that has the timer running from
> > another
> > > > > workbook, programatically, the Before_Close event will run the
> > Stoptimer
> > > > but
> > > > > it won't kill it. It will refire again.
> > > > >
> > > > > This has caused me ample problems.
> > > > >
> > > > > Try it
> > > > > "excelent" wrote:
> > > > >
> > > > > > Maby u can use this as inspiration
> > > > > >
> > > > > > http://pmexcelent.dk/FileMaster.xls
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Antonio]

It does not reopen if you run the sub in tta.xls manually.

If you run it programatically it does reopen.

I have tried it on several computers Office 2003 and 07, all the same.

Please let me know. Thanks.

"Bob Phillips" wrote:

> Well I have just tried it and it works fine for me. File B does not re-open
> at all.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Antonio" <[email protected]> wrote in message
> news:[email protected]...
> > I wish I were wrong, I really wish.
> >
> > Try this:
> >
> > tta.xls Module1
> >
> > Sub main()
> >
> > Workbooks("ttb.xls").Close SaveChanges:=False
> >
> > End Sub
> >
> >
> > ttb.xls ThisWorkbook
> >
> > Public Sub Workbook_Open()
> >
> > timer
> >
> > End Sub
> >
> > Public Sub Workbook_BeforeClose(cancel As Boolean)
> >
> > cancel_timer
> >
> >
> > End Sub
> >
> > ttb.xls Module1
> >
> > Public rt As Double
> >
> > Public Sub timer()
> >
> > rt = Now + TimeValue("00:00:02")
> >
> > Application.OnTime rt, "timer"
> >
> > End Sub
> >
> > Public Sub cancel_timer()
> >
> >
> > Application.OnTime rt, "timer", , False
> >
> >
> > End Sub
> >
> > Do this now:
> >
> > Open ttb.xls
> > Open tta.xls
> > Assing sub main() in tta.xls to a button, click on the button, it does
> close
> > ttb.xls but....ttb.xls reopens. This is not supposed to happen and it
> creates
> > all sorts of problems. If you know that I am wrong please correct me, you
> > would help me tremendously.
> >
> > There is a workaround, but it is not reliable.
> >
> >
> >
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > As long as you call it with exactly the same scheduled time as when you
> set
> > > it, it works fine.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "Antonio" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob,
> > > >
> > > > I have done intensive work on this issue.
> > > >
> > > > I know how it is suposed to be done but it just does not work. It is a
> > > bug,
> > > > an annoying one.
> > > >
> > > > Again, try closing a workbook with a live On Time procedure from a
> > > different
> > > > workbook, if you rely on the Before_Close event it does not work. It
> never
> > > > works.
> > > >
> > > > You have to call the kill timer sub independently and it does not
> always
> > > > work.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > You should kill the timer. Chip Pearson explains it all on
> > > > > http://www.cpearson.com/excel/ontime.htm
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace somewhere in email address with gmail if mailing direct)
> > > > >
> > > > > "Antonio" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hi Excelent,
> > > > > >
> > > > > > I have been looking at your code.
> > > > > >
> > > > > > Since you have worked with the Application On Time command I
> wonder if
> > > you
> > > > > > have run into the following difficulty:
> > > > > >
> > > > > > If you try to close the workbook that has the timer running from
> > > another
> > > > > > workbook, programatically, the Before_Close event will run the
> > > Stoptimer
> > > > > but
> > > > > > it won't kill it. It will refire again.
> > > > > >
> > > > > > This has caused me ample problems.
> > > > > >
> > > > > > Try it
> > > > > > "excelent" wrote:
> > > > > >
> > > > > > > Maby u can use this as inspiration
> > > > > > >
> > > > > > > http://pmexcelent.dk/FileMaster.xls
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

Sorry, I do get the problem. I added some debug code previously that
overrode it. Don't have a solution as yet, but I will post back if I think
of anything.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Antonio" <[email protected]> wrote in message
news:[email protected]...
> It does not reopen if you run the sub in tta.xls manually.
>
> If you run it programatically it does reopen.
>
> I have tried it on several computers Office 2003 and 07, all the same.
>
> Please let me know. Thanks.
>
> "Bob Phillips" wrote:
>
> > Well I have just tried it and it works fine for me. File B does not
re-open
> > at all.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Antonio" <[email protected]> wrote in message
> > news:[email protected]...
> > > I wish I were wrong, I really wish.
> > >
> > > Try this:
> > >
> > > tta.xls Module1
> > >
> > > Sub main()
> > >
> > > Workbooks("ttb.xls").Close SaveChanges:=False
> > >
> > > End Sub
> > >
> > >
> > > ttb.xls ThisWorkbook
> > >
> > > Public Sub Workbook_Open()
> > >
> > > timer
> > >
> > > End Sub
> > >
> > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > >
> > > cancel_timer
> > >
> > >
> > > End Sub
> > >
> > > ttb.xls Module1
> > >
> > > Public rt As Double
> > >
> > > Public Sub timer()
> > >
> > > rt = Now + TimeValue("00:00:02")
> > >
> > > Application.OnTime rt, "timer"
> > >
> > > End Sub
> > >
> > > Public Sub cancel_timer()
> > >
> > >
> > > Application.OnTime rt, "timer", , False
> > >
> > >
> > > End Sub
> > >
> > > Do this now:
> > >
> > > Open ttb.xls
> > > Open tta.xls
> > > Assing sub main() in tta.xls to a button, click on the button, it does
> > close
> > > ttb.xls but....ttb.xls reopens. This is not supposed to happen and it
> > creates
> > > all sorts of problems. If you know that I am wrong please correct me,
you
> > > would help me tremendously.
> > >
> > > There is a workaround, but it is not reliable.
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > As long as you call it with exactly the same scheduled time as when
you
> > set
> > > > it, it works fine.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "Antonio" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Bob,
> > > > >
> > > > > I have done intensive work on this issue.
> > > > >
> > > > > I know how it is suposed to be done but it just does not work. It
is a
> > > > bug,
> > > > > an annoying one.
> > > > >
> > > > > Again, try closing a workbook with a live On Time procedure from a
> > > > different
> > > > > workbook, if you rely on the Before_Close event it does not work.
It
> > never
> > > > > works.
> > > > >
> > > > > You have to call the kill timer sub independently and it does not
> > always
> > > > > work.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > You should kill the timer. Chip Pearson explains it all on
> > > > > > http://www.cpearson.com/excel/ontime.htm
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (replace somewhere in email address with gmail if mailing
direct)
> > > > > >
> > > > > > "Antonio" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Hi Excelent,
> > > > > > >
> > > > > > > I have been looking at your code.
> > > > > > >
> > > > > > > Since you have worked with the Application On Time command I
> > wonder if
> > > > you
> > > > > > > have run into the following difficulty:
> > > > > > >
> > > > > > > If you try to close the workbook that has the timer running
from
> > > > another
> > > > > > > workbook, programatically, the Before_Close event will run the
> > > > Stoptimer
> > > > > > but
> > > > > > > it won't kill it. It will refire again.
> > > > > > >
> > > > > > > This has caused me ample problems.
> > > > > > >
> > > > > > > Try it
> > > > > > > "excelent" wrote:
> > > > > > >
> > > > > > > > Maby u can use this as inspiration
> > > > > > > >
> > > > > > > > http://pmexcelent.dk/FileMaster.xls
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Antonio]

Hi Bob,

I appreciate your quality contribution to this forum, I learn a lot from
your comments. The issue is now in good hands.

My workaround is in tta.xls:

Application.Run "cancel_timer"
Workbooks("ttb.xls").Close SaveChanges:=False

The problem is that it does not work reliably. If you let the workbook (not
the simple test one, but a real big workbook) run for a while, when it comes
to be closed it throws an error. On Error Goto Next it is not a solution.
Perhaps it happens more on data intensive applications like mine.

Regards,

Antonio







"Bob Phillips" wrote:

> Sorry, I do get the problem. I added some debug code previously that
> overrode it. Don't have a solution as yet, but I will post back if I think
> of anything.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Antonio" <[email protected]> wrote in message
> news:[email protected]...
> > It does not reopen if you run the sub in tta.xls manually.
> >
> > If you run it programatically it does reopen.
> >
> > I have tried it on several computers Office 2003 and 07, all the same.
> >
> > Please let me know. Thanks.
> >
> > "Bob Phillips" wrote:
> >
> > > Well I have just tried it and it works fine for me. File B does not
> re-open
> > > at all.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "Antonio" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I wish I were wrong, I really wish.
> > > >
> > > > Try this:
> > > >
> > > > tta.xls Module1
> > > >
> > > > Sub main()
> > > >
> > > > Workbooks("ttb.xls").Close SaveChanges:=False
> > > >
> > > > End Sub
> > > >
> > > >
> > > > ttb.xls ThisWorkbook
> > > >
> > > > Public Sub Workbook_Open()
> > > >
> > > > timer
> > > >
> > > > End Sub
> > > >
> > > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > > >
> > > > cancel_timer
> > > >
> > > >
> > > > End Sub
> > > >
> > > > ttb.xls Module1
> > > >
> > > > Public rt As Double
> > > >
> > > > Public Sub timer()
> > > >
> > > > rt = Now + TimeValue("00:00:02")
> > > >
> > > > Application.OnTime rt, "timer"
> > > >
> > > > End Sub
> > > >
> > > > Public Sub cancel_timer()
> > > >
> > > >
> > > > Application.OnTime rt, "timer", , False
> > > >
> > > >
> > > > End Sub
> > > >
> > > > Do this now:
> > > >
> > > > Open ttb.xls
> > > > Open tta.xls
> > > > Assing sub main() in tta.xls to a button, click on the button, it does
> > > close
> > > > ttb.xls but....ttb.xls reopens. This is not supposed to happen and it
> > > creates
> > > > all sorts of problems. If you know that I am wrong please correct me,
> you
> > > > would help me tremendously.
> > > >
> > > > There is a workaround, but it is not reliable.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > As long as you call it with exactly the same scheduled time as when
> you
> > > set
> > > > > it, it works fine.
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace somewhere in email address with gmail if mailing direct)
> > > > >
> > > > > "Antonio" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hi Bob,
> > > > > >
> > > > > > I have done intensive work on this issue.
> > > > > >
> > > > > > I know how it is suposed to be done but it just does not work. It
> is a
> > > > > bug,
> > > > > > an annoying one.
> > > > > >
> > > > > > Again, try closing a workbook with a live On Time procedure from a
> > > > > different
> > > > > > workbook, if you rely on the Before_Close event it does not work.
> It
> > > never
> > > > > > works.
> > > > > >
> > > > > > You have to call the kill timer sub independently and it does not
> > > always
> > > > > > work.
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > You should kill the timer. Chip Pearson explains it all on
> > > > > > > http://www.cpearson.com/excel/ontime.htm
> > > > > > >
> > > > > > > --
> > > > > > > HTH
> > > > > > >
> > > > > > > Bob Phillips
> > > > > > >
> > > > > > > (replace somewhere in email address with gmail if mailing
> direct)
> > > > > > >
> > > > > > > "Antonio" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > Hi Excelent,
> > > > > > > >
> > > > > > > > I have been looking at your code.
> > > > > > > >
> > > > > > > > Since you have worked with the Application On Time command I
> > > wonder if
> > > > > you
> > > > > > > > have run into the following difficulty:
> > > > > > > >
> > > > > > > > If you try to close the workbook that has the timer running
> from
> > > > > another
> > > > > > > > workbook, programatically, the Before_Close event will run the
> > > > > Stoptimer
> > > > > > > but
> > > > > > > > it won't kill it. It will refire again.
> > > > > > > >
> > > > > > > > This has caused me ample problems.
> > > > > > > >
> > > > > > > > Try it
> > > > > > > > "excelent" wrote:
> > > > > > > >
> > > > > > > > > Maby u can use this as inspiration
> > > > > > > > >
> > > > > > > > > http://pmexcelent.dk/FileMaster.xls
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

Antonio,

I tried something like that, but the way that you have it I cannot see that
working at all. cancel_timer is in tlb not tla, so you need to qualify with
the workbook and that is where it failed for me.

I will continue trying so keep checking back (upto the end of the week).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Antonio" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> I appreciate your quality contribution to this forum, I learn a lot from
> your comments. The issue is now in good hands.
>
> My workaround is in tta.xls:
>
> Application.Run "cancel_timer"
> Workbooks("ttb.xls").Close SaveChanges:=False
>
> The problem is that it does not work reliably. If you let the workbook
(not
> the simple test one, but a real big workbook) run for a while, when it
comes
> to be closed it throws an error. On Error Goto Next it is not a solution.
> Perhaps it happens more on data intensive applications like mine.
>
> Regards,
>
> Antonio
>
>
>
>
>
>
>
> "Bob Phillips" wrote:
>
> > Sorry, I do get the problem. I added some debug code previously that
> > overrode it. Don't have a solution as yet, but I will post back if I
think
> > of anything.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Antonio" <[email protected]> wrote in message
> > news:[email protected]...
> > > It does not reopen if you run the sub in tta.xls manually.
> > >
> > > If you run it programatically it does reopen.
> > >
> > > I have tried it on several computers Office 2003 and 07, all the same.
> > >
> > > Please let me know. Thanks.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Well I have just tried it and it works fine for me. File B does not
> > re-open
> > > > at all.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "Antonio" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I wish I were wrong, I really wish.
> > > > >
> > > > > Try this:
> > > > >
> > > > > tta.xls Module1
> > > > >
> > > > > Sub main()
> > > > >
> > > > > Workbooks("ttb.xls").Close SaveChanges:=False
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > > ttb.xls ThisWorkbook
> > > > >
> > > > > Public Sub Workbook_Open()
> > > > >
> > > > > timer
> > > > >
> > > > > End Sub
> > > > >
> > > > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > > > >
> > > > > cancel_timer
> > > > >
> > > > >
> > > > > End Sub
> > > > >
> > > > > ttb.xls Module1
> > > > >
> > > > > Public rt As Double
> > > > >
> > > > > Public Sub timer()
> > > > >
> > > > > rt = Now + TimeValue("00:00:02")
> > > > >
> > > > > Application.OnTime rt, "timer"
> > > > >
> > > > > End Sub
> > > > >
> > > > > Public Sub cancel_timer()
> > > > >
> > > > >
> > > > > Application.OnTime rt, "timer", , False
> > > > >
> > > > >
> > > > > End Sub
> > > > >
> > > > > Do this now:
> > > > >
> > > > > Open ttb.xls
> > > > > Open tta.xls
> > > > > Assing sub main() in tta.xls to a button, click on the button, it
does
> > > > close
> > > > > ttb.xls but....ttb.xls reopens. This is not supposed to happen and
it
> > > > creates
> > > > > all sorts of problems. If you know that I am wrong please correct
me,
> > you
> > > > > would help me tremendously.
> > > > >
> > > > > There is a workaround, but it is not reliable.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > As long as you call it with exactly the same scheduled time as
when
> > you
> > > > set
> > > > > > it, it works fine.
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (replace somewhere in email address with gmail if mailing
direct)
> > > > > >
> > > > > > "Antonio" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Hi Bob,
> > > > > > >
> > > > > > > I have done intensive work on this issue.
> > > > > > >
> > > > > > > I know how it is suposed to be done but it just does not work.
It
> > is a
> > > > > > bug,
> > > > > > > an annoying one.
> > > > > > >
> > > > > > > Again, try closing a workbook with a live On Time procedure
from a
> > > > > > different
> > > > > > > workbook, if you rely on the Before_Close event it does not
work.
> > It
> > > > never
> > > > > > > works.
> > > > > > >
> > > > > > > You have to call the kill timer sub independently and it does
not
> > > > always
> > > > > > > work.
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > You should kill the timer. Chip Pearson explains it all on
> > > > > > > > http://www.cpearson.com/excel/ontime.htm
> > > > > > > >
> > > > > > > > --
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > Bob Phillips
> > > > > > > >
> > > > > > > > (replace somewhere in email address with gmail if mailing
> > direct)
> > > > > > > >
> > > > > > > > "Antonio" <[email protected]> wrote in
message
> > > > > > > > news:[email protected]...
> > > > > > > > > Hi Excelent,
> > > > > > > > >
> > > > > > > > > I have been looking at your code.
> > > > > > > > >
> > > > > > > > > Since you have worked with the Application On Time command
I
> > > > wonder if
> > > > > > you
> > > > > > > > > have run into the following difficulty:
> > > > > > > > >
> > > > > > > > > If you try to close the workbook that has the timer
running
> > from
> > > > > > another
> > > > > > > > > workbook, programatically, the Before_Close event will run
the
> > > > > > Stoptimer
> > > > > > > > but
> > > > > > > > > it won't kill it. It will refire again.
> > > > > > > > >
> > > > > > > > > This has caused me ample problems.
> > > > > > > > >
> > > > > > > > > Try it
> > > > > > > > > "excelent" wrote:
> > > > > > > > >
> > > > > > > > > > Maby u can use this as inspiration
> > > > > > > > > >
> > > > > > > > > > http://pmexcelent.dk/FileMaster.xls
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >