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