On Time Bug?

[Antonio]

Please help me with this one. Sorry to go over this again.

ttb.xls:

ThisWorkbook:

Option Explicit
Public Sub Workbook_Open()

timer

End Sub

Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before

cancel_timer

End Sub

Module1


Option Explicit
Public rt As Double

Public Sub timer()

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

Range("a1") = rt

Application.OnTime rt, "timer"

End Sub

Public Sub cancel_timer()


Application.OnTime rt, "timer", , False


End Sub

The above works fine. Beforeclose cancels the timer.

However, try to close ttb.xls from tta.xls using:



Sub main()

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

End Sub

You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
again shortly after.

Please tell me I am missing something, I hope it is not a bug.

After substantial time and extra work to circunvent this I want to get to
the bottom of the issue.

Many thanks,

Antonio

[Dave Peterson]

I put your code into a a workbook called booktimer.xls:

In the thisworkbook module:
Option Explicit
Public Sub Workbook_Open()
timer
End Sub
Public Sub Workbook_BeforeClose(cancel As Boolean)
cancel_timer
End Sub

In a general module:
Option Explicit
Public rt As Double
Public Sub timer()
rt = Now + TimeValue("00:00:03")
ThisWorkbook.Worksheets(1).Range("a1").Value = rt
MsgBox "hi from Timer"
Application.OnTime rt, "timer"
End Sub
Public Sub cancel_timer()
Application.OnTime rt, "timer", , False
End Sub

I changed to every 3 seconds for testing. And I fully qualified the range.

But neither of those are important.

I put this in a general module of a different workbook:
Option Explicit
Sub testme()
Workbooks("booktimer.xls").Close False
End Sub

It worked fine.

If you build two small workbooks with nothing else in them, does it work?

And one more debugging hint:

Public Sub cancel_timer()
MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
Application.OnTime rt, "timer", , False
End Sub

Is there anything in your code that is resetting the rt variable?

Do you have End (not End if/End sub/end function) in your code in the workbook
with the timer code?

Did you reset the code (Run|Reset inside the VBE)?

I don't have a guess, but maybe if you build those test workbooks, you'll see
that your skinnied down code actually works--and if you find that, you're going
to be busy debugging.

Antonio wrote:
>
> Please help me with this one. Sorry to go over this again.
>
> ttb.xls:
>
> ThisWorkbook:
>
> Option Explicit
> Public Sub Workbook_Open()
>
> timer
>
> End Sub
>
> Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
>
> cancel_timer
>
> End Sub
>
> Module1
>
> Option Explicit
> Public rt As Double
>
> Public Sub timer()
>
> rt = Now + TimeValue("00:00:10")
>
> Range("a1") = rt
>
> Application.OnTime rt, "timer"
>
> End Sub
>
> Public Sub cancel_timer()
>
> Application.OnTime rt, "timer", , False
>
> End Sub
>
> The above works fine. Beforeclose cancels the timer.
>
> However, try to close ttb.xls from tta.xls using:
>
> Sub main()
>
> Workbooks("ttb.xls").Close SaveChanges:=False
>
> End Sub
>
> You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> again shortly after.
>
> Please tell me I am missing something, I hope it is not a bug.
>
> After substantial time and extra work to circunvent this I want to get to
> the bottom of the issue.
>
> Many thanks,
>
> Antonio

--

Dave Peterson

[Dave Peterson]

Ps. I used xl2003, but I'm hoping that that isn't the problem.

Post your version and maybe someone can see if it is a bug in that version of
excel using the simplified code.

Dave Peterson wrote:
>
> I put your code into a a workbook called booktimer.xls:
>
> In the thisworkbook module:
> Option Explicit
> Public Sub Workbook_Open()
> timer
> End Sub
> Public Sub Workbook_BeforeClose(cancel As Boolean)
> cancel_timer
> End Sub
>
> In a general module:
> Option Explicit
> Public rt As Double
> Public Sub timer()
> rt = Now + TimeValue("00:00:03")
> ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> MsgBox "hi from Timer"
> Application.OnTime rt, "timer"
> End Sub
> Public Sub cancel_timer()
> Application.OnTime rt, "timer", , False
> End Sub
>
> I changed to every 3 seconds for testing. And I fully qualified the range.
>
> But neither of those are important.
>
> I put this in a general module of a different workbook:
> Option Explicit
> Sub testme()
> Workbooks("booktimer.xls").Close False
> End Sub
>
> It worked fine.
>
> If you build two small workbooks with nothing else in them, does it work?
>
> And one more debugging hint:
>
> Public Sub cancel_timer()
> MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> Application.OnTime rt, "timer", , False
> End Sub
>
> Is there anything in your code that is resetting the rt variable?
>
> Do you have End (not End if/End sub/end function) in your code in the workbook
> with the timer code?
>
> Did you reset the code (Run|Reset inside the VBE)?
>
> I don't have a guess, but maybe if you build those test workbooks, you'll see
> that your skinnied down code actually works--and if you find that, you're going
> to be busy debugging.
>
> Antonio wrote:
> >
> > Please help me with this one. Sorry to go over this again.
> >
> > ttb.xls:
> >
> > ThisWorkbook:
> >
> > Option Explicit
> > Public Sub Workbook_Open()
> >
> > timer
> >
> > End Sub
> >
> > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> >
> > cancel_timer
> >
> > End Sub
> >
> > Module1
> >
> > Option Explicit
> > Public rt As Double
> >
> > Public Sub timer()
> >
> > rt = Now + TimeValue("00:00:10")
> >
> > Range("a1") = rt
> >
> > Application.OnTime rt, "timer"
> >
> > End Sub
> >
> > Public Sub cancel_timer()
> >
> > Application.OnTime rt, "timer", , False
> >
> > End Sub
> >
> > The above works fine. Beforeclose cancels the timer.
> >
> > However, try to close ttb.xls from tta.xls using:
> >
> > Sub main()
> >
> > Workbooks("ttb.xls").Close SaveChanges:=False
> >
> > End Sub
> >
> > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > again shortly after.
> >
> > Please tell me I am missing something, I hope it is not a bug.
> >
> > After substantial time and extra work to circunvent this I want to get to
> > the bottom of the issue.
> >
> > Many thanks,
> >
> > Antonio
>
> --
>
> Dave Peterson

--

Dave Peterson

[Antonio]

Hi Dave,

Same problem.

I am using xl2003.

I did run your exact code. No End or Reset statements anywhere. PC just
rebooted.

I am beginning to think that my Excel is corrupted.

After installing Norton Internet Security 2006 I could not create an outlook
mail object.

I had to reinstall MS Office 2003 from scratch (repairing it did not work).

I have also tried running the test routines with NIS disabled, no change.

.... annoying one.

But again, many thanks.

Antonio



"Dave Peterson" wrote:

> Ps. I used xl2003, but I'm hoping that that isn't the problem.
>
> Post your version and maybe someone can see if it is a bug in that version of
> excel using the simplified code.
>
> Dave Peterson wrote:
> >
> > I put your code into a a workbook called booktimer.xls:
> >
> > In the thisworkbook module:
> > Option Explicit
> > Public Sub Workbook_Open()
> > timer
> > End Sub
> > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > cancel_timer
> > End Sub
> >
> > In a general module:
> > Option Explicit
> > Public rt As Double
> > Public Sub timer()
> > rt = Now + TimeValue("00:00:03")
> > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > MsgBox "hi from Timer"
> > Application.OnTime rt, "timer"
> > End Sub
> > Public Sub cancel_timer()
> > Application.OnTime rt, "timer", , False
> > End Sub
> >
> > I changed to every 3 seconds for testing. And I fully qualified the range.
> >
> > But neither of those are important.
> >
> > I put this in a general module of a different workbook:
> > Option Explicit
> > Sub testme()
> > Workbooks("booktimer.xls").Close False
> > End Sub
> >
> > It worked fine.
> >
> > If you build two small workbooks with nothing else in them, does it work?
> >
> > And one more debugging hint:
> >
> > Public Sub cancel_timer()
> > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > Application.OnTime rt, "timer", , False
> > End Sub
> >
> > Is there anything in your code that is resetting the rt variable?
> >
> > Do you have End (not End if/End sub/end function) in your code in the workbook
> > with the timer code?
> >
> > Did you reset the code (Run|Reset inside the VBE)?
> >
> > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > that your skinnied down code actually works--and if you find that, you're going
> > to be busy debugging.
> >
> > Antonio wrote:
> > >
> > > Please help me with this one. Sorry to go over this again.
> > >
> > > ttb.xls:
> > >
> > > ThisWorkbook:
> > >
> > > Option Explicit
> > > Public Sub Workbook_Open()
> > >
> > > timer
> > >
> > > End Sub
> > >
> > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > >
> > > cancel_timer
> > >
> > > End Sub
> > >
> > > Module1
> > >
> > > Option Explicit
> > > Public rt As Double
> > >
> > > Public Sub timer()
> > >
> > > rt = Now + TimeValue("00:00:10")
> > >
> > > Range("a1") = rt
> > >
> > > Application.OnTime rt, "timer"
> > >
> > > End Sub
> > >
> > > Public Sub cancel_timer()
> > >
> > > Application.OnTime rt, "timer", , False
> > >
> > > End Sub
> > >
> > > The above works fine. Beforeclose cancels the timer.
> > >
> > > However, try to close ttb.xls from tta.xls using:
> > >
> > > Sub main()
> > >
> > > Workbooks("ttb.xls").Close SaveChanges:=False
> > >
> > > End Sub
> > >
> > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > again shortly after.
> > >
> > > Please tell me I am missing something, I hope it is not a bug.
> > >
> > > After substantial time and extra work to circunvent this I want to get to
> > > the bottom of the issue.
> > >
> > > Many thanks,
> > >
> > > Antonio
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Dave Peterson
>

[Antonio]

FYI

Dear Sirs,

I am an advanced user of MS Office and knowledgeable about NIS.

The issue below did not happen again after reinstalling MS Office. It was
very likely a conflict between NIS and MS VBA.

The problem was that it could happen again. And it has.

The attached two spreadsheets contain Excel 2003 VBA code that does not work
on my computers with MS Office 2003 (fully updated) and NIS 2006. When the
button in tta.xls is clicked, tta.xls is closed but then it reopens. It
should not reopen and it has caused me plenty of time.

According to other discussion groups user, the code works fine on other
computers with MS Office 2003.

I have tried disabling NIS, no change. I have repaired MS Office, no change.


I am reluctant to uninstall NIS or MS Office, especially because I don’t
know if this or another issue will reoccur and because my MS Office
installation is a fresh one after NIS.

All my software is fully paid for and validated.

I upgraded from NIS 2005 to 2006 hoping for a fix to some other issues and
now I have this major uncertainties that can be very costly.

Can you please tell me all information you have about this potential
conflicts between MS Office and NIS and how to go about them?

Regards,


Antonio Salcedo



________________________________________
From: Symantec Technical Support [mailto:[email protected]]
Sent: 26 May 2006 12:47
To: Antonio Salcedo
Subject: RE:'Case=002-086-354'

Hello Antonio,

Thank you for contacting Symantec Online Technical Support.

I understand from your message that you are facing issue with sending
Outlook emails using Excel program.

Antonio, this issue can occur due to conflicts between Norton Internet
Security(NIS) and your VBA program.

In addition, as you have mentioned this issue could as happen if MS Office
is corrupted or not configured with your VBA program, since the issue is
resolved after reinstalling MS Office.

However, if the issue occurs again then to find the root cause of the issue
I suggest that you disable Norton Internet Security(NIS) and then check for
the issue.

To disable Norton Internet Security(NIS) 2006 follow the steps given below:

1. Right click on the Norton Internet Security icon in System tray.
2. Select Disable Norton Internet Security.
3. Open Norton Internet Security and click on Security and Turn "OFF"
4. Choose the time limit you want to Disable Security for and click ok.

Note: Please turn ON Norton Internet Security(NIS) after checking for the
issue.

If you require further assistance, please do not hesitate to contact us and
thank you for using Symantec software.

Regards,

Baaskar.R
Symantec Authorized Technical Support


"Dave Peterson" wrote:

> Ps. I used xl2003, but I'm hoping that that isn't the problem.
>
> Post your version and maybe someone can see if it is a bug in that version of
> excel using the simplified code.
>
> Dave Peterson wrote:
> >
> > I put your code into a a workbook called booktimer.xls:
> >
> > In the thisworkbook module:
> > Option Explicit
> > Public Sub Workbook_Open()
> > timer
> > End Sub
> > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > cancel_timer
> > End Sub
> >
> > In a general module:
> > Option Explicit
> > Public rt As Double
> > Public Sub timer()
> > rt = Now + TimeValue("00:00:03")
> > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > MsgBox "hi from Timer"
> > Application.OnTime rt, "timer"
> > End Sub
> > Public Sub cancel_timer()
> > Application.OnTime rt, "timer", , False
> > End Sub
> >
> > I changed to every 3 seconds for testing. And I fully qualified the range.
> >
> > But neither of those are important.
> >
> > I put this in a general module of a different workbook:
> > Option Explicit
> > Sub testme()
> > Workbooks("booktimer.xls").Close False
> > End Sub
> >
> > It worked fine.
> >
> > If you build two small workbooks with nothing else in them, does it work?
> >
> > And one more debugging hint:
> >
> > Public Sub cancel_timer()
> > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > Application.OnTime rt, "timer", , False
> > End Sub
> >
> > Is there anything in your code that is resetting the rt variable?
> >
> > Do you have End (not End if/End sub/end function) in your code in the workbook
> > with the timer code?
> >
> > Did you reset the code (Run|Reset inside the VBE)?
> >
> > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > that your skinnied down code actually works--and if you find that, you're going
> > to be busy debugging.
> >
> > Antonio wrote:
> > >
> > > Please help me with this one. Sorry to go over this again.
> > >
> > > ttb.xls:
> > >
> > > ThisWorkbook:
> > >
> > > Option Explicit
> > > Public Sub Workbook_Open()
> > >
> > > timer
> > >
> > > End Sub
> > >
> > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > >
> > > cancel_timer
> > >
> > > End Sub
> > >
> > > Module1
> > >
> > > Option Explicit
> > > Public rt As Double
> > >
> > > Public Sub timer()
> > >
> > > rt = Now + TimeValue("00:00:10")
> > >
> > > Range("a1") = rt
> > >
> > > Application.OnTime rt, "timer"
> > >
> > > End Sub
> > >
> > > Public Sub cancel_timer()
> > >
> > > Application.OnTime rt, "timer", , False
> > >
> > > End Sub
> > >
> > > The above works fine. Beforeclose cancels the timer.
> > >
> > > However, try to close ttb.xls from tta.xls using:
> > >
> > > Sub main()
> > >
> > > Workbooks("ttb.xls").Close SaveChanges:=False
> > >
> > > End Sub
> > >
> > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > again shortly after.
> > >
> > > Please tell me I am missing something, I hope it is not a bug.
> > >
> > > After substantial time and extra work to circunvent this I want to get to
> > > the bottom of the issue.
> > >
> > > Many thanks,
> > >
> > > Antonio
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Dave Peterson
>

[Antonio]

Hi Dave,

Are you running Norton Internet Security 2006 by any chance?

Thanks,

Antonio

"Dave Peterson" wrote:

> Ps. I used xl2003, but I'm hoping that that isn't the problem.
>
> Post your version and maybe someone can see if it is a bug in that version of
> excel using the simplified code.
>
> Dave Peterson wrote:
> >
> > I put your code into a a workbook called booktimer.xls:
> >
> > In the thisworkbook module:
> > Option Explicit
> > Public Sub Workbook_Open()
> > timer
> > End Sub
> > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > cancel_timer
> > End Sub
> >
> > In a general module:
> > Option Explicit
> > Public rt As Double
> > Public Sub timer()
> > rt = Now + TimeValue("00:00:03")
> > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > MsgBox "hi from Timer"
> > Application.OnTime rt, "timer"
> > End Sub
> > Public Sub cancel_timer()
> > Application.OnTime rt, "timer", , False
> > End Sub
> >
> > I changed to every 3 seconds for testing. And I fully qualified the range.
> >
> > But neither of those are important.
> >
> > I put this in a general module of a different workbook:
> > Option Explicit
> > Sub testme()
> > Workbooks("booktimer.xls").Close False
> > End Sub
> >
> > It worked fine.
> >
> > If you build two small workbooks with nothing else in them, does it work?
> >
> > And one more debugging hint:
> >
> > Public Sub cancel_timer()
> > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > Application.OnTime rt, "timer", , False
> > End Sub
> >
> > Is there anything in your code that is resetting the rt variable?
> >
> > Do you have End (not End if/End sub/end function) in your code in the workbook
> > with the timer code?
> >
> > Did you reset the code (Run|Reset inside the VBE)?
> >
> > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > that your skinnied down code actually works--and if you find that, you're going
> > to be busy debugging.
> >
> > Antonio wrote:
> > >
> > > Please help me with this one. Sorry to go over this again.
> > >
> > > ttb.xls:
> > >
> > > ThisWorkbook:
> > >
> > > Option Explicit
> > > Public Sub Workbook_Open()
> > >
> > > timer
> > >
> > > End Sub
> > >
> > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > >
> > > cancel_timer
> > >
> > > End Sub
> > >
> > > Module1
> > >
> > > Option Explicit
> > > Public rt As Double
> > >
> > > Public Sub timer()
> > >
> > > rt = Now + TimeValue("00:00:10")
> > >
> > > Range("a1") = rt
> > >
> > > Application.OnTime rt, "timer"
> > >
> > > End Sub
> > >
> > > Public Sub cancel_timer()
> > >
> > > Application.OnTime rt, "timer", , False
> > >
> > > End Sub
> > >
> > > The above works fine. Beforeclose cancels the timer.
> > >
> > > However, try to close ttb.xls from tta.xls using:
> > >
> > > Sub main()
> > >
> > > Workbooks("ttb.xls").Close SaveChanges:=False
> > >
> > > End Sub
> > >
> > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > again shortly after.
> > >
> > > Please tell me I am missing something, I hope it is not a bug.
> > >
> > > After substantial time and extra work to circunvent this I want to get to
> > > the bottom of the issue.
> > >
> > > Many thanks,
> > >
> > > Antonio
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Nope.

But good luck in your quest.

Please post back if you or Norton resolve it. Then google will have for the
next person.

Antonio wrote:
>
> Hi Dave,
>
> Are you running Norton Internet Security 2006 by any chance?
>
> Thanks,
>
> Antonio
>
> "Dave Peterson" wrote:
>
> > Ps. I used xl2003, but I'm hoping that that isn't the problem.
> >
> > Post your version and maybe someone can see if it is a bug in that version of
> > excel using the simplified code.
> >
> > Dave Peterson wrote:
> > >
> > > I put your code into a a workbook called booktimer.xls:
> > >
> > > In the thisworkbook module:
> > > Option Explicit
> > > Public Sub Workbook_Open()
> > > timer
> > > End Sub
> > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > > cancel_timer
> > > End Sub
> > >
> > > In a general module:
> > > Option Explicit
> > > Public rt As Double
> > > Public Sub timer()
> > > rt = Now + TimeValue("00:00:03")
> > > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > > MsgBox "hi from Timer"
> > > Application.OnTime rt, "timer"
> > > End Sub
> > > Public Sub cancel_timer()
> > > Application.OnTime rt, "timer", , False
> > > End Sub
> > >
> > > I changed to every 3 seconds for testing. And I fully qualified the range.
> > >
> > > But neither of those are important.
> > >
> > > I put this in a general module of a different workbook:
> > > Option Explicit
> > > Sub testme()
> > > Workbooks("booktimer.xls").Close False
> > > End Sub
> > >
> > > It worked fine.
> > >
> > > If you build two small workbooks with nothing else in them, does it work?
> > >
> > > And one more debugging hint:
> > >
> > > Public Sub cancel_timer()
> > > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > > Application.OnTime rt, "timer", , False
> > > End Sub
> > >
> > > Is there anything in your code that is resetting the rt variable?
> > >
> > > Do you have End (not End if/End sub/end function) in your code in the workbook
> > > with the timer code?
> > >
> > > Did you reset the code (Run|Reset inside the VBE)?
> > >
> > > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > > that your skinnied down code actually works--and if you find that, you're going
> > > to be busy debugging.
> > >
> > > Antonio wrote:
> > > >
> > > > Please help me with this one. Sorry to go over this again.
> > > >
> > > > ttb.xls:
> > > >
> > > > ThisWorkbook:
> > > >
> > > > Option Explicit
> > > > Public Sub Workbook_Open()
> > > >
> > > > timer
> > > >
> > > > End Sub
> > > >
> > > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > > >
> > > > cancel_timer
> > > >
> > > > End Sub
> > > >
> > > > Module1
> > > >
> > > > Option Explicit
> > > > Public rt As Double
> > > >
> > > > Public Sub timer()
> > > >
> > > > rt = Now + TimeValue("00:00:10")
> > > >
> > > > Range("a1") = rt
> > > >
> > > > Application.OnTime rt, "timer"
> > > >
> > > > End Sub
> > > >
> > > > Public Sub cancel_timer()
> > > >
> > > > Application.OnTime rt, "timer", , False
> > > >
> > > > End Sub
> > > >
> > > > The above works fine. Beforeclose cancels the timer.
> > > >
> > > > However, try to close ttb.xls from tta.xls using:
> > > >
> > > > Sub main()
> > > >
> > > > Workbooks("ttb.xls").Close SaveChanges:=False
> > > >
> > > > End Sub
> > > >
> > > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > > again shortly after.
> > > >
> > > > Please tell me I am missing something, I hope it is not a bug.
> > > >
> > > > After substantial time and extra work to circunvent this I want to get to
> > > > the bottom of the issue.
> > > >
> > > > Many thanks,
> > > >
> > > > Antonio
> > >
> > > --
> > >
> > > Dave Peterson
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Antonio]

Hi Dave again,

I have tried the code in different computers and they all show the same
problem, the workbook reopens by itself.

I have tried one of my PCs with Microsoft Excel 2007 Beta 2. NIS 2006
installed

I have tried a different one with Office 2003, no NIS

I have tried two others, same issue.

Are you sure it does not refire on your PC?

Thanks,

Antonio

"Dave Peterson" wrote:

> Nope.
>
> But good luck in your quest.
>
> Please post back if you or Norton resolve it. Then google will have for the
> next person.
>
> Antonio wrote:
> >
> > Hi Dave,
> >
> > Are you running Norton Internet Security 2006 by any chance?
> >
> > Thanks,
> >
> > Antonio
> >
> > "Dave Peterson" wrote:
> >
> > > Ps. I used xl2003, but I'm hoping that that isn't the problem.
> > >
> > > Post your version and maybe someone can see if it is a bug in that version of
> > > excel using the simplified code.
> > >
> > > Dave Peterson wrote:
> > > >
> > > > I put your code into a a workbook called booktimer.xls:
> > > >
> > > > In the thisworkbook module:
> > > > Option Explicit
> > > > Public Sub Workbook_Open()
> > > > timer
> > > > End Sub
> > > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > > > cancel_timer
> > > > End Sub
> > > >
> > > > In a general module:
> > > > Option Explicit
> > > > Public rt As Double
> > > > Public Sub timer()
> > > > rt = Now + TimeValue("00:00:03")
> > > > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > > > MsgBox "hi from Timer"
> > > > Application.OnTime rt, "timer"
> > > > End Sub
> > > > Public Sub cancel_timer()
> > > > Application.OnTime rt, "timer", , False
> > > > End Sub
> > > >
> > > > I changed to every 3 seconds for testing. And I fully qualified the range.
> > > >
> > > > But neither of those are important.
> > > >
> > > > I put this in a general module of a different workbook:
> > > > Option Explicit
> > > > Sub testme()
> > > > Workbooks("booktimer.xls").Close False
> > > > End Sub
> > > >
> > > > It worked fine.
> > > >
> > > > If you build two small workbooks with nothing else in them, does it work?
> > > >
> > > > And one more debugging hint:
> > > >
> > > > Public Sub cancel_timer()
> > > > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > > > Application.OnTime rt, "timer", , False
> > > > End Sub
> > > >
> > > > Is there anything in your code that is resetting the rt variable?
> > > >
> > > > Do you have End (not End if/End sub/end function) in your code in the workbook
> > > > with the timer code?
> > > >
> > > > Did you reset the code (Run|Reset inside the VBE)?
> > > >
> > > > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > > > that your skinnied down code actually works--and if you find that, you're going
> > > > to be busy debugging.
> > > >
> > > > Antonio wrote:
> > > > >
> > > > > Please help me with this one. Sorry to go over this again.
> > > > >
> > > > > ttb.xls:
> > > > >
> > > > > ThisWorkbook:
> > > > >
> > > > > Option Explicit
> > > > > Public Sub Workbook_Open()
> > > > >
> > > > > timer
> > > > >
> > > > > End Sub
> > > > >
> > > > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > > > >
> > > > > cancel_timer
> > > > >
> > > > > End Sub
> > > > >
> > > > > Module1
> > > > >
> > > > > Option Explicit
> > > > > Public rt As Double
> > > > >
> > > > > Public Sub timer()
> > > > >
> > > > > rt = Now + TimeValue("00:00:10")
> > > > >
> > > > > Range("a1") = rt
> > > > >
> > > > > Application.OnTime rt, "timer"
> > > > >
> > > > > End Sub
> > > > >
> > > > > Public Sub cancel_timer()
> > > > >
> > > > > Application.OnTime rt, "timer", , False
> > > > >
> > > > > End Sub
> > > > >
> > > > > The above works fine. Beforeclose cancels the timer.
> > > > >
> > > > > However, try to close ttb.xls from tta.xls using:
> > > > >
> > > > > Sub main()
> > > > >
> > > > > Workbooks("ttb.xls").Close SaveChanges:=False
> > > > >
> > > > > End Sub
> > > > >
> > > > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > > > again shortly after.
> > > > >
> > > > > Please tell me I am missing something, I hope it is not a bug.
> > > > >
> > > > > After substantial time and extra work to circunvent this I want to get to
> > > > > the bottom of the issue.
> > > > >
> > > > > Many thanks,
> > > > >
> > > > > Antonio
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Positive.

Antonio wrote:
>
> Hi Dave again,
>
> I have tried the code in different computers and they all show the same
> problem, the workbook reopens by itself.
>
> I have tried one of my PCs with Microsoft Excel 2007 Beta 2. NIS 2006
> installed
>
> I have tried a different one with Office 2003, no NIS
>
> I have tried two others, same issue.
>
> Are you sure it does not refire on your PC?
>
> Thanks,
>
> Antonio
>
> "Dave Peterson" wrote:
>
> > Nope.
> >
> > But good luck in your quest.
> >
> > Please post back if you or Norton resolve it. Then google will have for the
> > next person.
> >
> > Antonio wrote:
> > >
> > > Hi Dave,
> > >
> > > Are you running Norton Internet Security 2006 by any chance?
> > >
> > > Thanks,
> > >
> > > Antonio
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Ps. I used xl2003, but I'm hoping that that isn't the problem.
> > > >
> > > > Post your version and maybe someone can see if it is a bug in that version of
> > > > excel using the simplified code.
> > > >
> > > > Dave Peterson wrote:
> > > > >
> > > > > I put your code into a a workbook called booktimer.xls:
> > > > >
> > > > > In the thisworkbook module:
> > > > > Option Explicit
> > > > > Public Sub Workbook_Open()
> > > > > timer
> > > > > End Sub
> > > > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > > > > cancel_timer
> > > > > End Sub
> > > > >
> > > > > In a general module:
> > > > > Option Explicit
> > > > > Public rt As Double
> > > > > Public Sub timer()
> > > > > rt = Now + TimeValue("00:00:03")
> > > > > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > > > > MsgBox "hi from Timer"
> > > > > Application.OnTime rt, "timer"
> > > > > End Sub
> > > > > Public Sub cancel_timer()
> > > > > Application.OnTime rt, "timer", , False
> > > > > End Sub
> > > > >
> > > > > I changed to every 3 seconds for testing. And I fully qualified the range.
> > > > >
> > > > > But neither of those are important.
> > > > >
> > > > > I put this in a general module of a different workbook:
> > > > > Option Explicit
> > > > > Sub testme()
> > > > > Workbooks("booktimer.xls").Close False
> > > > > End Sub
> > > > >
> > > > > It worked fine.
> > > > >
> > > > > If you build two small workbooks with nothing else in them, does it work?
> > > > >
> > > > > And one more debugging hint:
> > > > >
> > > > > Public Sub cancel_timer()
> > > > > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > > > > Application.OnTime rt, "timer", , False
> > > > > End Sub
> > > > >
> > > > > Is there anything in your code that is resetting the rt variable?
> > > > >
> > > > > Do you have End (not End if/End sub/end function) in your code in the workbook
> > > > > with the timer code?
> > > > >
> > > > > Did you reset the code (Run|Reset inside the VBE)?
> > > > >
> > > > > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > > > > that your skinnied down code actually works--and if you find that, you're going
> > > > > to be busy debugging.
> > > > >
> > > > > Antonio wrote:
> > > > > >
> > > > > > Please help me with this one. Sorry to go over this again.
> > > > > >
> > > > > > ttb.xls:
> > > > > >
> > > > > > ThisWorkbook:
> > > > > >
> > > > > > Option Explicit
> > > > > > Public Sub Workbook_Open()
> > > > > >
> > > > > > timer
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > > > > >
> > > > > > cancel_timer
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > Module1
> > > > > >
> > > > > > Option Explicit
> > > > > > Public rt As Double
> > > > > >
> > > > > > Public Sub timer()
> > > > > >
> > > > > > rt = Now + TimeValue("00:00:10")
> > > > > >
> > > > > > Range("a1") = rt
> > > > > >
> > > > > > Application.OnTime rt, "timer"
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > Public Sub cancel_timer()
> > > > > >
> > > > > > Application.OnTime rt, "timer", , False
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > The above works fine. Beforeclose cancels the timer.
> > > > > >
> > > > > > However, try to close ttb.xls from tta.xls using:
> > > > > >
> > > > > > Sub main()
> > > > > >
> > > > > > Workbooks("ttb.xls").Close SaveChanges:=False
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > > > > again shortly after.
> > > > > >
> > > > > > Please tell me I am missing something, I hope it is not a bug.
> > > > > >
> > > > > > After substantial time and extra work to circunvent this I want to get to
> > > > > > the bottom of the issue.
> > > > > >
> > > > > > Many thanks,
> > > > > >
> > > > > > Antonio
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Antonio]

All the PCs tested were using my spreadsheets.

Could you email me your spreadsheets to test on my system?

If you give me your email I will post my files to you.

Thanks,

Antonio

[email protected]


"Dave Peterson" wrote:

> Positive.
>
> Antonio wrote:
> >
> > Hi Dave again,
> >
> > I have tried the code in different computers and they all show the same
> > problem, the workbook reopens by itself.
> >
> > I have tried one of my PCs with Microsoft Excel 2007 Beta 2. NIS 2006
> > installed
> >
> > I have tried a different one with Office 2003, no NIS
> >
> > I have tried two others, same issue.
> >
> > Are you sure it does not refire on your PC?
> >
> > Thanks,
> >
> > Antonio
> >
> > "Dave Peterson" wrote:
> >
> > > Nope.
> > >
> > > But good luck in your quest.
> > >
> > > Please post back if you or Norton resolve it. Then google will have for the
> > > next person.
> > >
> > > Antonio wrote:
> > > >
> > > > Hi Dave,
> > > >
> > > > Are you running Norton Internet Security 2006 by any chance?
> > > >
> > > > Thanks,
> > > >
> > > > Antonio
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Ps. I used xl2003, but I'm hoping that that isn't the problem.
> > > > >
> > > > > Post your version and maybe someone can see if it is a bug in that version of
> > > > > excel using the simplified code.
> > > > >
> > > > > Dave Peterson wrote:
> > > > > >
> > > > > > I put your code into a a workbook called booktimer.xls:
> > > > > >
> > > > > > In the thisworkbook module:
> > > > > > Option Explicit
> > > > > > Public Sub Workbook_Open()
> > > > > > timer
> > > > > > End Sub
> > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > > > > > cancel_timer
> > > > > > End Sub
> > > > > >
> > > > > > In a general module:
> > > > > > Option Explicit
> > > > > > Public rt As Double
> > > > > > Public Sub timer()
> > > > > > rt = Now + TimeValue("00:00:03")
> > > > > > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > > > > > MsgBox "hi from Timer"
> > > > > > Application.OnTime rt, "timer"
> > > > > > End Sub
> > > > > > Public Sub cancel_timer()
> > > > > > Application.OnTime rt, "timer", , False
> > > > > > End Sub
> > > > > >
> > > > > > I changed to every 3 seconds for testing. And I fully qualified the range.
> > > > > >
> > > > > > But neither of those are important.
> > > > > >
> > > > > > I put this in a general module of a different workbook:
> > > > > > Option Explicit
> > > > > > Sub testme()
> > > > > > Workbooks("booktimer.xls").Close False
> > > > > > End Sub
> > > > > >
> > > > > > It worked fine.
> > > > > >
> > > > > > If you build two small workbooks with nothing else in them, does it work?
> > > > > >
> > > > > > And one more debugging hint:
> > > > > >
> > > > > > Public Sub cancel_timer()
> > > > > > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > > > > > Application.OnTime rt, "timer", , False
> > > > > > End Sub
> > > > > >
> > > > > > Is there anything in your code that is resetting the rt variable?
> > > > > >
> > > > > > Do you have End (not End if/End sub/end function) in your code in the workbook
> > > > > > with the timer code?
> > > > > >
> > > > > > Did you reset the code (Run|Reset inside the VBE)?
> > > > > >
> > > > > > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > > > > > that your skinnied down code actually works--and if you find that, you're going
> > > > > > to be busy debugging.
> > > > > >
> > > > > > Antonio wrote:
> > > > > > >
> > > > > > > Please help me with this one. Sorry to go over this again.
> > > > > > >
> > > > > > > ttb.xls:
> > > > > > >
> > > > > > > ThisWorkbook:
> > > > > > >
> > > > > > > Option Explicit
> > > > > > > Public Sub Workbook_Open()
> > > > > > >
> > > > > > > timer
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > > > > > >
> > > > > > > cancel_timer
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > Module1
> > > > > > >
> > > > > > > Option Explicit
> > > > > > > Public rt As Double
> > > > > > >
> > > > > > > Public Sub timer()
> > > > > > >
> > > > > > > rt = Now + TimeValue("00:00:10")
> > > > > > >
> > > > > > > Range("a1") = rt
> > > > > > >
> > > > > > > Application.OnTime rt, "timer"
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > Public Sub cancel_timer()
> > > > > > >
> > > > > > > Application.OnTime rt, "timer", , False
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > The above works fine. Beforeclose cancels the timer.
> > > > > > >
> > > > > > > However, try to close ttb.xls from tta.xls using:
> > > > > > >
> > > > > > > Sub main()
> > > > > > >
> > > > > > > Workbooks("ttb.xls").Close SaveChanges:=False
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > > > > > again shortly after.
> > > > > > >
> > > > > > > Please tell me I am missing something, I hope it is not a bug.
> > > > > > >
> > > > > > > After substantial time and extra work to circunvent this I want to get to
> > > > > > > the bottom of the issue.
> > > > > > >
> > > > > > > Many thanks,
> > > > > > >
> > > > > > > Antonio
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I went back and tried it a few times more.

It seemed like sometimes it would work--if I started the code manually from the
VBE (Run|Run)--but if I assigned the close macro to a button, the other file
opened up again.

I modified the code in the general module of the book with the timer:

Option Explicit
Public rt As Double
Public Sub timer()
rt = Now + TimeValue("00:00:03")
ThisWorkbook.Worksheets(1).Range("a1") = rt
MsgBox "hi from Timer"
Application.OnTime rt, "timer"
End Sub
Public Sub cancel_timer()
MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
On Error Resume Next 'Added this line
Application.OnTime rt, "timer", , False
End Sub

And the code that closed the other workbook:

Option Explicit
Sub testme02()
Dim OtherWkbk As Workbook
Set OtherWkbk = Workbooks("booktimer.xls")
Application.Run "'" & OtherWkbk.Name & "'!cancel_timer"
Application.EnableEvents = False
OtherWkbk.Close savechanges:=False
Application.EnableEvents = True
End Sub

You may not want to disable events. It depends on if you have anything in that
workbook_beforeclose that has to run.




Antonio wrote:
>
> Hi Dave again,
>
> I have tried the code in different computers and they all show the same
> problem, the workbook reopens by itself.
>
> I have tried one of my PCs with Microsoft Excel 2007 Beta 2. NIS 2006
> installed
>
> I have tried a different one with Office 2003, no NIS
>
> I have tried two others, same issue.
>
> Are you sure it does not refire on your PC?
>
> Thanks,
>
> Antonio
>
> "Dave Peterson" wrote:
>
> > Nope.
> >
> > But good luck in your quest.
> >
> > Please post back if you or Norton resolve it. Then google will have for the
> > next person.
> >
> > Antonio wrote:
> > >
> > > Hi Dave,
> > >
> > > Are you running Norton Internet Security 2006 by any chance?
> > >
> > > Thanks,
> > >
> > > Antonio
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Ps. I used xl2003, but I'm hoping that that isn't the problem.
> > > >
> > > > Post your version and maybe someone can see if it is a bug in that version of
> > > > excel using the simplified code.
> > > >
> > > > Dave Peterson wrote:
> > > > >
> > > > > I put your code into a a workbook called booktimer.xls:
> > > > >
> > > > > In the thisworkbook module:
> > > > > Option Explicit
> > > > > Public Sub Workbook_Open()
> > > > > timer
> > > > > End Sub
> > > > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > > > > cancel_timer
> > > > > End Sub
> > > > >
> > > > > In a general module:
> > > > > Option Explicit
> > > > > Public rt As Double
> > > > > Public Sub timer()
> > > > > rt = Now + TimeValue("00:00:03")
> > > > > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > > > > MsgBox "hi from Timer"
> > > > > Application.OnTime rt, "timer"
> > > > > End Sub
> > > > > Public Sub cancel_timer()
> > > > > Application.OnTime rt, "timer", , False
> > > > > End Sub
> > > > >
> > > > > I changed to every 3 seconds for testing. And I fully qualified the range.
> > > > >
> > > > > But neither of those are important.
> > > > >
> > > > > I put this in a general module of a different workbook:
> > > > > Option Explicit
> > > > > Sub testme()
> > > > > Workbooks("booktimer.xls").Close False
> > > > > End Sub
> > > > >
> > > > > It worked fine.
> > > > >
> > > > > If you build two small workbooks with nothing else in them, does it work?
> > > > >
> > > > > And one more debugging hint:
> > > > >
> > > > > Public Sub cancel_timer()
> > > > > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > > > > Application.OnTime rt, "timer", , False
> > > > > End Sub
> > > > >
> > > > > Is there anything in your code that is resetting the rt variable?
> > > > >
> > > > > Do you have End (not End if/End sub/end function) in your code in the workbook
> > > > > with the timer code?
> > > > >
> > > > > Did you reset the code (Run|Reset inside the VBE)?
> > > > >
> > > > > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > > > > that your skinnied down code actually works--and if you find that, you're going
> > > > > to be busy debugging.
> > > > >
> > > > > Antonio wrote:
> > > > > >
> > > > > > Please help me with this one. Sorry to go over this again.
> > > > > >
> > > > > > ttb.xls:
> > > > > >
> > > > > > ThisWorkbook:
> > > > > >
> > > > > > Option Explicit
> > > > > > Public Sub Workbook_Open()
> > > > > >
> > > > > > timer
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > > > > >
> > > > > > cancel_timer
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > Module1
> > > > > >
> > > > > > Option Explicit
> > > > > > Public rt As Double
> > > > > >
> > > > > > Public Sub timer()
> > > > > >
> > > > > > rt = Now + TimeValue("00:00:10")
> > > > > >
> > > > > > Range("a1") = rt
> > > > > >
> > > > > > Application.OnTime rt, "timer"
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > Public Sub cancel_timer()
> > > > > >
> > > > > > Application.OnTime rt, "timer", , False
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > The above works fine. Beforeclose cancels the timer.
> > > > > >
> > > > > > However, try to close ttb.xls from tta.xls using:
> > > > > >
> > > > > > Sub main()
> > > > > >
> > > > > > Workbooks("ttb.xls").Close SaveChanges:=False
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > > > > again shortly after.
> > > > > >
> > > > > > Please tell me I am missing something, I hope it is not a bug.
> > > > > >
> > > > > > After substantial time and extra work to circunvent this I want to get to
> > > > > > the bottom of the issue.
> > > > > >
> > > > > > Many thanks,
> > > > > >
> > > > > > Antonio
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Antonio]

Very interesting.

1. It brings everybody back to the same position. It also works fine for me
when the code is run manually.

(I also learned quite a few things from the Symantec tech support, about the
interactions between NIS and MS, although it was not the issue, it seems)

2. I agree with the rewriting of your code. Application.Run
.....!cancel_timer was also my solution. The problem is that it requires
modifying the code of all other routines that need to close the workbook with
the timer, that is bad enough.

Plus, it is not very reliable. Some times it does not work.


I cannnot disable events, before close has plenty of other things. But why
is this necessary? the On Error Resume Next in the cancel_timer avoids
throwing an error if the timer has already been cancelled.



"Dave Peterson" wrote:

> I went back and tried it a few times more.
>
> It seemed like sometimes it would work--if I started the code manually from the
> VBE (Run|Run)--but if I assigned the close macro to a button, the other file
> opened up again.
>
> I modified the code in the general module of the book with the timer:
>
> Option Explicit
> Public rt As Double
> Public Sub timer()
> rt = Now + TimeValue("00:00:03")
> ThisWorkbook.Worksheets(1).Range("a1") = rt
> MsgBox "hi from Timer"
> Application.OnTime rt, "timer"
> End Sub
> Public Sub cancel_timer()
> MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> On Error Resume Next 'Added this line
> Application.OnTime rt, "timer", , False
> End Sub
>
> And the code that closed the other workbook:
>
> Option Explicit
> Sub testme02()
> Dim OtherWkbk As Workbook
> Set OtherWkbk = Workbooks("booktimer.xls")
> Application.Run "'" & OtherWkbk.Name & "'!cancel_timer"
> Application.EnableEvents = False
> OtherWkbk.Close savechanges:=False
> Application.EnableEvents = True
> End Sub
>
> You may not want to disable events. It depends on if you have anything in that
> workbook_beforeclose that has to run.
>
>
>
>
> Antonio wrote:
> >
> > Hi Dave again,
> >
> > I have tried the code in different computers and they all show the same
> > problem, the workbook reopens by itself.
> >
> > I have tried one of my PCs with Microsoft Excel 2007 Beta 2. NIS 2006
> > installed
> >
> > I have tried a different one with Office 2003, no NIS
> >
> > I have tried two others, same issue.
> >
> > Are you sure it does not refire on your PC?
> >
> > Thanks,
> >
> > Antonio
> >
> > "Dave Peterson" wrote:
> >
> > > Nope.
> > >
> > > But good luck in your quest.
> > >
> > > Please post back if you or Norton resolve it. Then google will have for the
> > > next person.
> > >
> > > Antonio wrote:
> > > >
> > > > Hi Dave,
> > > >
> > > > Are you running Norton Internet Security 2006 by any chance?
> > > >
> > > > Thanks,
> > > >
> > > > Antonio
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Ps. I used xl2003, but I'm hoping that that isn't the problem.
> > > > >
> > > > > Post your version and maybe someone can see if it is a bug in that version of
> > > > > excel using the simplified code.
> > > > >
> > > > > Dave Peterson wrote:
> > > > > >
> > > > > > I put your code into a a workbook called booktimer.xls:
> > > > > >
> > > > > > In the thisworkbook module:
> > > > > > Option Explicit
> > > > > > Public Sub Workbook_Open()
> > > > > > timer
> > > > > > End Sub
> > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > > > > > cancel_timer
> > > > > > End Sub
> > > > > >
> > > > > > In a general module:
> > > > > > Option Explicit
> > > > > > Public rt As Double
> > > > > > Public Sub timer()
> > > > > > rt = Now + TimeValue("00:00:03")
> > > > > > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > > > > > MsgBox "hi from Timer"
> > > > > > Application.OnTime rt, "timer"
> > > > > > End Sub
> > > > > > Public Sub cancel_timer()
> > > > > > Application.OnTime rt, "timer", , False
> > > > > > End Sub
> > > > > >
> > > > > > I changed to every 3 seconds for testing. And I fully qualified the range.
> > > > > >
> > > > > > But neither of those are important.
> > > > > >
> > > > > > I put this in a general module of a different workbook:
> > > > > > Option Explicit
> > > > > > Sub testme()
> > > > > > Workbooks("booktimer.xls").Close False
> > > > > > End Sub
> > > > > >
> > > > > > It worked fine.
> > > > > >
> > > > > > If you build two small workbooks with nothing else in them, does it work?
> > > > > >
> > > > > > And one more debugging hint:
> > > > > >
> > > > > > Public Sub cancel_timer()
> > > > > > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > > > > > Application.OnTime rt, "timer", , False
> > > > > > End Sub
> > > > > >
> > > > > > Is there anything in your code that is resetting the rt variable?
> > > > > >
> > > > > > Do you have End (not End if/End sub/end function) in your code in the workbook
> > > > > > with the timer code?
> > > > > >
> > > > > > Did you reset the code (Run|Reset inside the VBE)?
> > > > > >
> > > > > > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > > > > > that your skinnied down code actually works--and if you find that, you're going
> > > > > > to be busy debugging.
> > > > > >
> > > > > > Antonio wrote:
> > > > > > >
> > > > > > > Please help me with this one. Sorry to go over this again.
> > > > > > >
> > > > > > > ttb.xls:
> > > > > > >
> > > > > > > ThisWorkbook:
> > > > > > >
> > > > > > > Option Explicit
> > > > > > > Public Sub Workbook_Open()
> > > > > > >
> > > > > > > timer
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > > > > > >
> > > > > > > cancel_timer
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > Module1
> > > > > > >
> > > > > > > Option Explicit
> > > > > > > Public rt As Double
> > > > > > >
> > > > > > > Public Sub timer()
> > > > > > >
> > > > > > > rt = Now + TimeValue("00:00:10")
> > > > > > >
> > > > > > > Range("a1") = rt
> > > > > > >
> > > > > > > Application.OnTime rt, "timer"
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > Public Sub cancel_timer()
> > > > > > >
> > > > > > > Application.OnTime rt, "timer", , False
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > The above works fine. Beforeclose cancels the timer.
> > > > > > >
> > > > > > > However, try to close ttb.xls from tta.xls using:
> > > > > > >
> > > > > > > Sub main()
> > > > > > >
> > > > > > > Workbooks("ttb.xls").Close SaveChanges:=False
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > > > > > again shortly after.
> > > > > > >
> > > > > > > Please tell me I am missing something, I hope it is not a bug.
> > > > > > >
> > > > > > > After substantial time and extra work to circunvent this I want to get to
> > > > > > > the bottom of the issue.
> > > > > > >
> > > > > > > Many thanks,
> > > > > > >
> > > > > > > Antonio
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I didn't have the "on error resume next" in the workbook_beforeclose event in my
original test workbook.

I wasn't sure if you had other code in that event. So I thought I'd mention it
as an option.

Antonio wrote:
>
> Very interesting.
>
> 1. It brings everybody back to the same position. It also works fine for me
> when the code is run manually.
>
> (I also learned quite a few things from the Symantec tech support, about the
> interactions between NIS and MS, although it was not the issue, it seems)
>
> 2. I agree with the rewriting of your code. Application.Run
> ....!cancel_timer was also my solution. The problem is that it requires
> modifying the code of all other routines that need to close the workbook with
> the timer, that is bad enough.
>
> Plus, it is not very reliable. Some times it does not work.
>
> I cannnot disable events, before close has plenty of other things. But why
> is this necessary? the On Error Resume Next in the cancel_timer avoids
> throwing an error if the timer has already been cancelled.
>
> "Dave Peterson" wrote:
>
> > I went back and tried it a few times more.
> >
> > It seemed like sometimes it would work--if I started the code manually from the
> > VBE (Run|Run)--but if I assigned the close macro to a button, the other file
> > opened up again.
> >
> > I modified the code in the general module of the book with the timer:
> >
> > Option Explicit
> > Public rt As Double
> > Public Sub timer()
> > rt = Now + TimeValue("00:00:03")
> > ThisWorkbook.Worksheets(1).Range("a1") = rt
> > MsgBox "hi from Timer"
> > Application.OnTime rt, "timer"
> > End Sub
> > Public Sub cancel_timer()
> > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > On Error Resume Next 'Added this line
> > Application.OnTime rt, "timer", , False
> > End Sub
> >
> > And the code that closed the other workbook:
> >
> > Option Explicit
> > Sub testme02()
> > Dim OtherWkbk As Workbook
> > Set OtherWkbk = Workbooks("booktimer.xls")
> > Application.Run "'" & OtherWkbk.Name & "'!cancel_timer"
> > Application.EnableEvents = False
> > OtherWkbk.Close savechanges:=False
> > Application.EnableEvents = True
> > End Sub
> >
> > You may not want to disable events. It depends on if you have anything in that
> > workbook_beforeclose that has to run.
> >
> >
> >
> >
> > Antonio wrote:
> > >
> > > Hi Dave again,
> > >
> > > I have tried the code in different computers and they all show the same
> > > problem, the workbook reopens by itself.
> > >
> > > I have tried one of my PCs with Microsoft Excel 2007 Beta 2. NIS 2006
> > > installed
> > >
> > > I have tried a different one with Office 2003, no NIS
> > >
> > > I have tried two others, same issue.
> > >
> > > Are you sure it does not refire on your PC?
> > >
> > > Thanks,
> > >
> > > Antonio
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Nope.
> > > >
> > > > But good luck in your quest.
> > > >
> > > > Please post back if you or Norton resolve it. Then google will have for the
> > > > next person.
> > > >
> > > > Antonio wrote:
> > > > >
> > > > > Hi Dave,
> > > > >
> > > > > Are you running Norton Internet Security 2006 by any chance?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Antonio
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Ps. I used xl2003, but I'm hoping that that isn't the problem.
> > > > > >
> > > > > > Post your version and maybe someone can see if it is a bug in that version of
> > > > > > excel using the simplified code.
> > > > > >
> > > > > > Dave Peterson wrote:
> > > > > > >
> > > > > > > I put your code into a a workbook called booktimer.xls:
> > > > > > >
> > > > > > > In the thisworkbook module:
> > > > > > > Option Explicit
> > > > > > > Public Sub Workbook_Open()
> > > > > > > timer
> > > > > > > End Sub
> > > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > > > > > > cancel_timer
> > > > > > > End Sub
> > > > > > >
> > > > > > > In a general module:
> > > > > > > Option Explicit
> > > > > > > Public rt As Double
> > > > > > > Public Sub timer()
> > > > > > > rt = Now + TimeValue("00:00:03")
> > > > > > > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > > > > > > MsgBox "hi from Timer"
> > > > > > > Application.OnTime rt, "timer"
> > > > > > > End Sub
> > > > > > > Public Sub cancel_timer()
> > > > > > > Application.OnTime rt, "timer", , False
> > > > > > > End Sub
> > > > > > >
> > > > > > > I changed to every 3 seconds for testing. And I fully qualified the range.
> > > > > > >
> > > > > > > But neither of those are important.
> > > > > > >
> > > > > > > I put this in a general module of a different workbook:
> > > > > > > Option Explicit
> > > > > > > Sub testme()
> > > > > > > Workbooks("booktimer.xls").Close False
> > > > > > > End Sub
> > > > > > >
> > > > > > > It worked fine.
> > > > > > >
> > > > > > > If you build two small workbooks with nothing else in them, does it work?
> > > > > > >
> > > > > > > And one more debugging hint:
> > > > > > >
> > > > > > > Public Sub cancel_timer()
> > > > > > > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > > > > > > Application.OnTime rt, "timer", , False
> > > > > > > End Sub
> > > > > > >
> > > > > > > Is there anything in your code that is resetting the rt variable?
> > > > > > >
> > > > > > > Do you have End (not End if/End sub/end function) in your code in the workbook
> > > > > > > with the timer code?
> > > > > > >
> > > > > > > Did you reset the code (Run|Reset inside the VBE)?
> > > > > > >
> > > > > > > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > > > > > > that your skinnied down code actually works--and if you find that, you're going
> > > > > > > to be busy debugging.
> > > > > > >
> > > > > > > Antonio wrote:
> > > > > > > >
> > > > > > > > Please help me with this one. Sorry to go over this again.
> > > > > > > >
> > > > > > > > ttb.xls:
> > > > > > > >
> > > > > > > > ThisWorkbook:
> > > > > > > >
> > > > > > > > Option Explicit
> > > > > > > > Public Sub Workbook_Open()
> > > > > > > >
> > > > > > > > timer
> > > > > > > >
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > > > > > > >
> > > > > > > > cancel_timer
> > > > > > > >
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > Module1
> > > > > > > >
> > > > > > > > Option Explicit
> > > > > > > > Public rt As Double
> > > > > > > >
> > > > > > > > Public Sub timer()
> > > > > > > >
> > > > > > > > rt = Now + TimeValue("00:00:10")
> > > > > > > >
> > > > > > > > Range("a1") = rt
> > > > > > > >
> > > > > > > > Application.OnTime rt, "timer"
> > > > > > > >
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > Public Sub cancel_timer()
> > > > > > > >
> > > > > > > > Application.OnTime rt, "timer", , False
> > > > > > > >
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > The above works fine. Beforeclose cancels the timer.
> > > > > > > >
> > > > > > > > However, try to close ttb.xls from tta.xls using:
> > > > > > > >
> > > > > > > > Sub main()
> > > > > > > >
> > > > > > > > Workbooks("ttb.xls").Close SaveChanges:=False
> > > > > > > >
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > > > > > > again shortly after.
> > > > > > > >
> > > > > > > > Please tell me I am missing something, I hope it is not a bug.
> > > > > > > >
> > > > > > > > After substantial time and extra work to circunvent this I want to get to
> > > > > > > > the bottom of the issue.
> > > > > > > >
> > > > > > > > Many thanks,
> > > > > > > >
> > > > > > > > Antonio
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Antonio]

So Dave, do you agree that this behaviour shows the existence of a bug?

Should we let Microsoft know?

How? By writing a suggestion in this forum?



"Dave Peterson" wrote:

> I didn't have the "on error resume next" in the workbook_beforeclose event in my
> original test workbook.
>
> I wasn't sure if you had other code in that event. So I thought I'd mention it
> as an option.
>
> Antonio wrote:
> >
> > Very interesting.
> >
> > 1. It brings everybody back to the same position. It also works fine for me
> > when the code is run manually.
> >
> > (I also learned quite a few things from the Symantec tech support, about the
> > interactions between NIS and MS, although it was not the issue, it seems)
> >
> > 2. I agree with the rewriting of your code. Application.Run
> > ....!cancel_timer was also my solution. The problem is that it requires
> > modifying the code of all other routines that need to close the workbook with
> > the timer, that is bad enough.
> >
> > Plus, it is not very reliable. Some times it does not work.
> >
> > I cannnot disable events, before close has plenty of other things. But why
> > is this necessary? the On Error Resume Next in the cancel_timer avoids
> > throwing an error if the timer has already been cancelled.
> >
> > "Dave Peterson" wrote:
> >
> > > I went back and tried it a few times more.
> > >
> > > It seemed like sometimes it would work--if I started the code manually from the
> > > VBE (Run|Run)--but if I assigned the close macro to a button, the other file
> > > opened up again.
> > >
> > > I modified the code in the general module of the book with the timer:
> > >
> > > Option Explicit
> > > Public rt As Double
> > > Public Sub timer()
> > > rt = Now + TimeValue("00:00:03")
> > > ThisWorkbook.Worksheets(1).Range("a1") = rt
> > > MsgBox "hi from Timer"
> > > Application.OnTime rt, "timer"
> > > End Sub
> > > Public Sub cancel_timer()
> > > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > > On Error Resume Next 'Added this line
> > > Application.OnTime rt, "timer", , False
> > > End Sub
> > >
> > > And the code that closed the other workbook:
> > >
> > > Option Explicit
> > > Sub testme02()
> > > Dim OtherWkbk As Workbook
> > > Set OtherWkbk = Workbooks("booktimer.xls")
> > > Application.Run "'" & OtherWkbk.Name & "'!cancel_timer"
> > > Application.EnableEvents = False
> > > OtherWkbk.Close savechanges:=False
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > You may not want to disable events. It depends on if you have anything in that
> > > workbook_beforeclose that has to run.
> > >
> > >
> > >
> > >
> > > Antonio wrote:
> > > >
> > > > Hi Dave again,
> > > >
> > > > I have tried the code in different computers and they all show the same
> > > > problem, the workbook reopens by itself.
> > > >
> > > > I have tried one of my PCs with Microsoft Excel 2007 Beta 2. NIS 2006
> > > > installed
> > > >
> > > > I have tried a different one with Office 2003, no NIS
> > > >
> > > > I have tried two others, same issue.
> > > >
> > > > Are you sure it does not refire on your PC?
> > > >
> > > > Thanks,
> > > >
> > > > Antonio
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Nope.
> > > > >
> > > > > But good luck in your quest.
> > > > >
> > > > > Please post back if you or Norton resolve it. Then google will have for the
> > > > > next person.
> > > > >
> > > > > Antonio wrote:
> > > > > >
> > > > > > Hi Dave,
> > > > > >
> > > > > > Are you running Norton Internet Security 2006 by any chance?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Antonio
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > Ps. I used xl2003, but I'm hoping that that isn't the problem.
> > > > > > >
> > > > > > > Post your version and maybe someone can see if it is a bug in that version of
> > > > > > > excel using the simplified code.
> > > > > > >
> > > > > > > Dave Peterson wrote:
> > > > > > > >
> > > > > > > > I put your code into a a workbook called booktimer.xls:
> > > > > > > >
> > > > > > > > In the thisworkbook module:
> > > > > > > > Option Explicit
> > > > > > > > Public Sub Workbook_Open()
> > > > > > > > timer
> > > > > > > > End Sub
> > > > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > > > > > > > cancel_timer
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > In a general module:
> > > > > > > > Option Explicit
> > > > > > > > Public rt As Double
> > > > > > > > Public Sub timer()
> > > > > > > > rt = Now + TimeValue("00:00:03")
> > > > > > > > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > > > > > > > MsgBox "hi from Timer"
> > > > > > > > Application.OnTime rt, "timer"
> > > > > > > > End Sub
> > > > > > > > Public Sub cancel_timer()
> > > > > > > > Application.OnTime rt, "timer", , False
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > I changed to every 3 seconds for testing. And I fully qualified the range.
> > > > > > > >
> > > > > > > > But neither of those are important.
> > > > > > > >
> > > > > > > > I put this in a general module of a different workbook:
> > > > > > > > Option Explicit
> > > > > > > > Sub testme()
> > > > > > > > Workbooks("booktimer.xls").Close False
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > It worked fine.
> > > > > > > >
> > > > > > > > If you build two small workbooks with nothing else in them, does it work?
> > > > > > > >
> > > > > > > > And one more debugging hint:
> > > > > > > >
> > > > > > > > Public Sub cancel_timer()
> > > > > > > > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > > > > > > > Application.OnTime rt, "timer", , False
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > Is there anything in your code that is resetting the rt variable?
> > > > > > > >
> > > > > > > > Do you have End (not End if/End sub/end function) in your code in the workbook
> > > > > > > > with the timer code?
> > > > > > > >
> > > > > > > > Did you reset the code (Run|Reset inside the VBE)?
> > > > > > > >
> > > > > > > > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > > > > > > > that your skinnied down code actually works--and if you find that, you're going
> > > > > > > > to be busy debugging.
> > > > > > > >
> > > > > > > > Antonio wrote:
> > > > > > > > >
> > > > > > > > > Please help me with this one. Sorry to go over this again.
> > > > > > > > >
> > > > > > > > > ttb.xls:
> > > > > > > > >
> > > > > > > > > ThisWorkbook:
> > > > > > > > >
> > > > > > > > > Option Explicit
> > > > > > > > > Public Sub Workbook_Open()
> > > > > > > > >
> > > > > > > > > timer
> > > > > > > > >
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > > > > > > > >
> > > > > > > > > cancel_timer
> > > > > > > > >
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > Module1
> > > > > > > > >
> > > > > > > > > Option Explicit
> > > > > > > > > Public rt As Double
> > > > > > > > >
> > > > > > > > > Public Sub timer()
> > > > > > > > >
> > > > > > > > > rt = Now + TimeValue("00:00:10")
> > > > > > > > >
> > > > > > > > > Range("a1") = rt
> > > > > > > > >
> > > > > > > > > Application.OnTime rt, "timer"
> > > > > > > > >
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > Public Sub cancel_timer()
> > > > > > > > >
> > > > > > > > > Application.OnTime rt, "timer", , False
> > > > > > > > >
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > The above works fine. Beforeclose cancels the timer.
> > > > > > > > >
> > > > > > > > > However, try to close ttb.xls from tta.xls using:
> > > > > > > > >
> > > > > > > > > Sub main()
> > > > > > > > >
> > > > > > > > > Workbooks("ttb.xls").Close SaveChanges:=False
> > > > > > > > >
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > > > > > > > again shortly after.
> > > > > > > > >
> > > > > > > > > Please tell me I am missing something, I hope it is not a bug.
> > > > > > > > >
> > > > > > > > > After substantial time and extra work to circunvent this I want to get to
> > > > > > > > > the bottom of the issue.
> > > > > > > > >
> > > > > > > > > Many thanks,
> > > > > > > > >
> > > > > > > > > Antonio
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

It doesn't look like it behaves correctly to me.

But since you have a workaround (even though you may not like it), I'm not sure
it would be changed--but it could turn into a knowledge base article and be
documented.

In fact, I didn't search the KB to see if it's already documented. You may want
to search there before you do more.

Antonio wrote:
>
> So Dave, do you agree that this behaviour shows the existence of a bug?
>
> Should we let Microsoft know?
>
> How? By writing a suggestion in this forum?
>
> "Dave Peterson" wrote:
>
> > I didn't have the "on error resume next" in the workbook_beforeclose event in my
> > original test workbook.
> >
> > I wasn't sure if you had other code in that event. So I thought I'd mention it
> > as an option.
> >
> > Antonio wrote:
> > >
> > > Very interesting.
> > >
> > > 1. It brings everybody back to the same position. It also works fine for me
> > > when the code is run manually.
> > >
> > > (I also learned quite a few things from the Symantec tech support, about the
> > > interactions between NIS and MS, although it was not the issue, it seems)
> > >
> > > 2. I agree with the rewriting of your code. Application.Run
> > > ....!cancel_timer was also my solution. The problem is that it requires
> > > modifying the code of all other routines that need to close the workbook with
> > > the timer, that is bad enough.
> > >
> > > Plus, it is not very reliable. Some times it does not work.
> > >
> > > I cannnot disable events, before close has plenty of other things. But why
> > > is this necessary? the On Error Resume Next in the cancel_timer avoids
> > > throwing an error if the timer has already been cancelled.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I went back and tried it a few times more.
> > > >
> > > > It seemed like sometimes it would work--if I started the code manually from the
> > > > VBE (Run|Run)--but if I assigned the close macro to a button, the other file
> > > > opened up again.
> > > >
> > > > I modified the code in the general module of the book with the timer:
> > > >
> > > > Option Explicit
> > > > Public rt As Double
> > > > Public Sub timer()
> > > > rt = Now + TimeValue("00:00:03")
> > > > ThisWorkbook.Worksheets(1).Range("a1") = rt
> > > > MsgBox "hi from Timer"
> > > > Application.OnTime rt, "timer"
> > > > End Sub
> > > > Public Sub cancel_timer()
> > > > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > > > On Error Resume Next 'Added this line
> > > > Application.OnTime rt, "timer", , False
> > > > End Sub
> > > >
> > > > And the code that closed the other workbook:
> > > >
> > > > Option Explicit
> > > > Sub testme02()
> > > > Dim OtherWkbk As Workbook
> > > > Set OtherWkbk = Workbooks("booktimer.xls")
> > > > Application.Run "'" & OtherWkbk.Name & "'!cancel_timer"
> > > > Application.EnableEvents = False
> > > > OtherWkbk.Close savechanges:=False
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > > You may not want to disable events. It depends on if you have anything in that
> > > > workbook_beforeclose that has to run.
> > > >
> > > >
> > > >
> > > >
> > > > Antonio wrote:
> > > > >
> > > > > Hi Dave again,
> > > > >
> > > > > I have tried the code in different computers and they all show the same
> > > > > problem, the workbook reopens by itself.
> > > > >
> > > > > I have tried one of my PCs with Microsoft Excel 2007 Beta 2. NIS 2006
> > > > > installed
> > > > >
> > > > > I have tried a different one with Office 2003, no NIS
> > > > >
> > > > > I have tried two others, same issue.
> > > > >
> > > > > Are you sure it does not refire on your PC?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Antonio
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Nope.
> > > > > >
> > > > > > But good luck in your quest.
> > > > > >
> > > > > > Please post back if you or Norton resolve it. Then google will have for the
> > > > > > next person.
> > > > > >
> > > > > > Antonio wrote:
> > > > > > >
> > > > > > > Hi Dave,
> > > > > > >
> > > > > > > Are you running Norton Internet Security 2006 by any chance?
> > > > > > >
> > > > > > > Thanks,
> > > > > > >
> > > > > > > Antonio
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > Ps. I used xl2003, but I'm hoping that that isn't the problem.
> > > > > > > >
> > > > > > > > Post your version and maybe someone can see if it is a bug in that version of
> > > > > > > > excel using the simplified code.
> > > > > > > >
> > > > > > > > Dave Peterson wrote:
> > > > > > > > >
> > > > > > > > > I put your code into a a workbook called booktimer.xls:
> > > > > > > > >
> > > > > > > > > In the thisworkbook module:
> > > > > > > > > Option Explicit
> > > > > > > > > Public Sub Workbook_Open()
> > > > > > > > > timer
> > > > > > > > > End Sub
> > > > > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean)
> > > > > > > > > cancel_timer
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > In a general module:
> > > > > > > > > Option Explicit
> > > > > > > > > Public rt As Double
> > > > > > > > > Public Sub timer()
> > > > > > > > > rt = Now + TimeValue("00:00:03")
> > > > > > > > > ThisWorkbook.Worksheets(1).Range("a1").Value = rt
> > > > > > > > > MsgBox "hi from Timer"
> > > > > > > > > Application.OnTime rt, "timer"
> > > > > > > > > End Sub
> > > > > > > > > Public Sub cancel_timer()
> > > > > > > > > Application.OnTime rt, "timer", , False
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > I changed to every 3 seconds for testing. And I fully qualified the range.
> > > > > > > > >
> > > > > > > > > But neither of those are important.
> > > > > > > > >
> > > > > > > > > I put this in a general module of a different workbook:
> > > > > > > > > Option Explicit
> > > > > > > > > Sub testme()
> > > > > > > > > Workbooks("booktimer.xls").Close False
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > It worked fine.
> > > > > > > > >
> > > > > > > > > If you build two small workbooks with nothing else in them, does it work?
> > > > > > > > >
> > > > > > > > > And one more debugging hint:
> > > > > > > > >
> > > > > > > > > Public Sub cancel_timer()
> > > > > > > > > MsgBox rt & vbLf & ThisWorkbook.Worksheets(1).Range("a1")
> > > > > > > > > Application.OnTime rt, "timer", , False
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > Is there anything in your code that is resetting the rt variable?
> > > > > > > > >
> > > > > > > > > Do you have End (not End if/End sub/end function) in your code in the workbook
> > > > > > > > > with the timer code?
> > > > > > > > >
> > > > > > > > > Did you reset the code (Run|Reset inside the VBE)?
> > > > > > > > >
> > > > > > > > > I don't have a guess, but maybe if you build those test workbooks, you'll see
> > > > > > > > > that your skinnied down code actually works--and if you find that, you're going
> > > > > > > > > to be busy debugging.
> > > > > > > > >
> > > > > > > > > Antonio wrote:
> > > > > > > > > >
> > > > > > > > > > Please help me with this one. Sorry to go over this again.
> > > > > > > > > >
> > > > > > > > > > ttb.xls:
> > > > > > > > > >
> > > > > > > > > > ThisWorkbook:
> > > > > > > > > >
> > > > > > > > > > Option Explicit
> > > > > > > > > > Public Sub Workbook_Open()
> > > > > > > > > >
> > > > > > > > > > timer
> > > > > > > > > >
> > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > > > > > > Public Sub Workbook_BeforeClose(cancel As Boolean) 'was Private Sub.... before
> > > > > > > > > >
> > > > > > > > > > cancel_timer
> > > > > > > > > >
> > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > > > > > > Module1
> > > > > > > > > >
> > > > > > > > > > Option Explicit
> > > > > > > > > > Public rt As Double
> > > > > > > > > >
> > > > > > > > > > Public Sub timer()
> > > > > > > > > >
> > > > > > > > > > rt = Now + TimeValue("00:00:10")
> > > > > > > > > >
> > > > > > > > > > Range("a1") = rt
> > > > > > > > > >
> > > > > > > > > > Application.OnTime rt, "timer"
> > > > > > > > > >
> > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > > > > > > Public Sub cancel_timer()
> > > > > > > > > >
> > > > > > > > > > Application.OnTime rt, "timer", , False
> > > > > > > > > >
> > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > > > > > > The above works fine. Beforeclose cancels the timer.
> > > > > > > > > >
> > > > > > > > > > However, try to close ttb.xls from tta.xls using:
> > > > > > > > > >
> > > > > > > > > > Sub main()
> > > > > > > > > >
> > > > > > > > > > Workbooks("ttb.xls").Close SaveChanges:=False
> > > > > > > > > >
> > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > > > > > > You will see that it gives you no error, closes ttb.xls. BUT ttb.xls reopens
> > > > > > > > > > again shortly after.
> > > > > > > > > >
> > > > > > > > > > Please tell me I am missing something, I hope it is not a bug.
> > > > > > > > > >
> > > > > > > > > > After substantial time and extra work to circunvent this I want to get to
> > > > > > > > > > the bottom of the issue.
> > > > > > > > > >
> > > > > > > > > > Many thanks,
> > > > > > > > > >
> > > > > > > > > > Antonio
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > Dave Peterson
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson