BeforeClose failure

JH

New Member
Joined
Nov 23, 2005
Messages
16
Situation:
Excel 2000
Two or more workbooks contain BeforeClose procedures.
When each workbook is closed separately the BeforeClose routine in each workbook works flawlessly.

Problem:
If the workbooks are open and Excel is closed, then only the first
BeforeClose routine is executed, and Excel and all of the workbooks
remain open, including the one whose BeforeClose procedure runs.

Experimentation:
There are no "On Error Resume Next" statements in the BeforeClose code.
I've stepped though the BeforeClose routines and there are no VBA errors generated.
The "failure to terminate" seems to occur, after the first BeforeClose routine
finishes but when Excel fails to continue and execute the next BeforeClose
routine.

Solution:
???

TIA
JH
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
L

Legacy 98055

Guest
I created a similiar setup and could not reproduce your problem. All procedures executed as they should. Version 2003. Post your code. If Cancel is True then neither the workbook or the application will close.

Tom
 

JH

New Member
Joined
Nov 23, 2005
Messages
16
In this situation I don't think posting the code would give you insight
into the problem because of the routines that are run. However, I have
already considered and checked the possibility of a "Cancel = True"
situation.

If you would like to examine the interaction of the two files in question and step through the code, you can download excel.zip at
http://biab.8m.com/holiday/excel.zip.

Once you unzip the "excel.zip" you will have two files, "labpass.xls" and "ard.xls". To recreate the problem I've experienced, load "labpass.xls" first and then load "ard.xls". Once loaded, terminate Excel using the "X" close button in the right hand corner. On my PC, running 98Se, when the "X" button is clicked, the "BeforeClose" routine runs but the workbooks are are not closed. One of the custom toolbars is deleted but that is all. When I click the "X" again, the "BeforeClose" routine runs again, this time closing the workbooks and Excel.

The problem does not occur when "ard.xls" is loaded before "labpass.xls".

The same problem can be created by loading "labpass.xls" and then loading any other selection of workbooks. Excel's "X" close button will not terminate the workbooks and Excel program unless "labpass.xls" has the focus when the "X" button is clicked.

If you find the problem, let me know.
JH
 
L

Legacy 98055

Guest
Hi JH.

Am going through some of my posts. I did download your files but was unable to figure out what the problem was. Sorry. :)
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446

ADVERTISEMENT

This is quite unique.

Setting up several break points and testing a few scenarios, it appears the Ard.xls before_Close macro is not even called when opening the Labpass.xls file first, a separately opening Ard.xls.

I was able to find the "leak"

For some reason, it first runs the before close code on the inactive workbook first, so it would be bypassing the before close routine in Ard.xls entirely.

Oddly, it runs the entire before close routine as expected in labpass.xls and then just waits....

In my case, after testing the various scenarios...

I opened ARD.xls

then I opened labpass.xls

I activated ARD.xls and hit close. ARD.xls before close code ran as expected, followed by the labpass.xls code.


and both ran fine. When I removed the line lappass.xls ran first, and did not close either.

I am not sure why this is happening. I have office 2000, XP as well..

I added this line to the bottom of the lappass.xls Before Close code and all worked as it should, for every file open, regardless of order of opening.

ActiveWorkbook.Close
 

JH

New Member
Joined
Nov 23, 2005
Messages
16
Thanks, Gibbs,

I appreciate your response. I think I have tried the solution you suggest,
but, since I've tried so many different experiments, I decided I would
implement your suggestion in case my memory has a leak. :LOL:

In essence, adding "ActiveWorkbook.Close" to the end of the BeforeClose
routine, forces the BeforeClose routine to run twice, so when I
close "labpass.xls' the closing questions are presented twice which can be
confusing. However, with the addition in place, I ran some experiments.

When I loaded "labpass.xls" first and "ard.xls" secondly and "ard.xls" had
the focus when Excel was closed then routines did indeed close the
application.

When I loaded "labpass.xls" first and "ard.xls" secondly and "labpass.xls"
was active when Excel was closed then "labpass.xls" was closed but the user
was left staring at an open "ard.xls" and Excel did not close as expected.

When I loaded "ard.xls" first and "labpass.xls" secondly and "labpass.xls"
had the focus when Excel was closed then "labpass.xls" closed but I still
had an open "ard.xls" and Excel did not close as expected.

When I loaded "ard.xls" first and "labpass.xls" secondly and "ard.xls" was
active when Excel was closed then Excel crashed with the message "This
program has performed an illegal operation... etc" with details "EXCEL
caused an invalid page fault in module EXCEL.EXE at 0187:30110f8b".

I'm still searching for a solution and I readily welcome suggestions from
any of the many knowlegeable participants of this forum. At this point, I'm
wondering if the problem is my code or an "undocumented feature" within
Excel. I assume its my code but ... where?

Thanks...
 

JH

New Member
Joined
Nov 23, 2005
Messages
16
To all who are interested, I finally found the source of the problem. The reference to "Windows("labpass.xls").Activate" during the BeforeClose routine in my "Labpass.xls caused the before close procedures to not function properly under certain conditions. From different experiments, it seems to me that when Excel is terminated, Excel processes each BeforeClose procedure in the order from the first workbook that was opened to the last one that was opened. Once all the BeforeClose routines have been executed, Excel then loops and closes each workbook in that same order.

If you wish to experiment with this situation:
1. Create two workbooks, Book1.xls and Book2.xls.
2. In Book1 add this BeforeClose routine and save.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Windows("Book1.xls").Activate
MsgBox ("Book1.xls..... " & Book1)
End Sub

3. In Book2 add this BeforeClose routine and save.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Book2 = ActiveWorkbook.Name
MsgBox ("Book2.xls..... " & Book2)
End Sub

4. Note1: The Message box will display the the following data:
"BeforeClose active Workbook..... " & ActiveWorkbook.Name

5. Note2: Notice when both workbooks are open and then Excel is terminated, the BeforecClose routines follow the same order in which you opened the workbooks (1st name in the MsgBox).

6. Now change the BeforeClose routine in Book1.xls with this addition:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Book1 = ActiveWorkbook.Name
Windows("Book1.xls").Activate
MsgBox ("Book1.xls..... " & Book1)
End Sub

7. Save Book1.xls and close Excel.
8. Now open Book1.xls and Book2.xls respectively.
9. Lastly, terminate Excel with the "X" in the right top corner.

The application does not close until your repeat the procedure.

Lesson learn:
Avoid using Windows("XXX.xls").Activate in a BeforeClose routine.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,234
Messages
5,571,042
Members
412,358
Latest member
Eng Muhammed
Top