Closing workbooks (oh you'll see)

[alkatran]

Alright, here's what I want to do:

I have a main workbook whose 'close' event is fired when the "X" is pressed to close excel. At the time, the focus is on another workbook (opened via the first one with code)

I have tried and tried and tried but no matter what I do I either end up with:
The workbooks close IFF I'm focused on the main workbook
The main workbook won't close when focused on second workbook
(tried moving focus to first workbook first, no effect)
Excel crashes

Some code:
On Error Resume Next
Dim Wkb As Workbook
Static Recurse As Boolean

If Recurse Then
Exit Sub
End If
'temp code to stop bug
'preferably save and close the whole program or just that workbook
'grey bar shows up if just close that workbook
If WkbIntro.Name <> ActiveWorkbook.Name Then
MsgBox "timbrplans can only be closed from the intro page.", vbExclamation, "timbrplans"
Cancel = True
Exit Sub
End If
'sets updating, events to false, and status bar to "..."
Safety.SetSettings False, False, True, "Shutting Down..."

'Just closing the program here, no real effect
CheckSystemData
Recurse = True
EnableKeys True
EnableMenus True
Application.Caption = "Excel"

'Save and close all of our workbooks
'by checking if they are open
For Each Wkb In Workbooks
Select Case LCase(Wkb.Name)
Case "tbm_intro.xls"
Case "tbm_homes.xls", "tbm_swing.xls", "tbm_sheds.xls", _
"tbm_backyardorganizer.xls", "tbm_decks.xls", _
"tbm_Bunkie.xls", "tbm_estimator.xls", "tbm_garages.xls"
SaveMe Wkb
Wkb.Close False
Case Else
Cancel = True
End Select
Next Wkb

SaveMe ThisWorkbook
'default settings for next excel run
Safety.RestoreSettings

'if an unknown workbook was open, dont kill all of excel
If Cancel Then
Application.WindowState = xlNormal
ThisWorkbook.Close False
End If

[RobDog888]

Could you verify that I have it correct.

You open the main workbook and then open a second one from
the main one. Then when you close the main one you are trying
to close the second one but it disallows it.

It sounds like the order of operations needs defining. All
secondary workbooks should be closed BEFORE the main
workbook is closed.

How close am I :D

[alkatran]

Way off:

Check the code, the secondaries are all closed first. I open the main, open the second via the main, then hit "X" in the second.

The close event in the main is fired (I put a breakpoint in both close events, only the main oen is fired) then runs the sub below.

It does some cleanup, saves closes the secondary workbook, then saves and closes the main. (are the workbook.close statements getting in the way of the closing?)

I end up with the main one open and saved.

I tried putting "thisworkbook.close" at the bottom, but it still leaves excel open, with no workbook (application.quit didn't work, either).

[alkatran]

By the way this is excel 97

[RobDog888]

If application.quit doesnt work then you may have a hidden process
stil open. Check taskmanager for any other instances
of "EXCEL.EXE". Then try again. I will look closer at your code
again, but without all the code its hard to debug.

[alkatran]

Excel.exe is obviously still in the task manager if I can see it and open new workbooks. :bigyello:

The puzzle here is: why does the sub work if and only if it is called when the focus is on the main workbook (where the sub is stored)?

[RobDog888]

Perhaps I was not clear, but I was refering to multiple instances
of EXCEL.EXE not a single instance. :grin:

After looking at your sample code a bit more, I would suggest
that you use the complete objects instead of shortcuts like
ThisWorkbook. This will allow you to debug where the issue may
be coming from.

[alkatran]

"thisworkbook" refers to the workbook the code is in, the main workbook. I actually changed it from workbooks("tbm_intro.xls") because it looks better.

ActiveWorkbook is needed because... well sometimes this is called from outside "ThisWorkbook"!

No, there are not multiple instances of excel.exe

I also have captions (the "SaveMe" function is a simple three liner that changes the status bar caption + saves) that tell me it's following the correct order of operations.

Hell, I've gone through it using F8 a few times.

[RobDog888]

I know what ThisWorkBook is I was trying to point out that
"thisworkbook" can change depending on the active workbook,
not just the one that has the code running. This is why I was
suggesting complete object references. Its like the way
AvtiveWorkbook changes between workbooks depending on the
wb in focus.

[alkatran]

Originally posted by RobDog888
I know what ThisWorkBook is I was trying to point out that
"thisworkbook" can change depending on the active workbook,
not just the one that has the code running. This is why I was
suggesting complete object references. Its like the way
AvtiveWorkbook changes between workbooks depending on the
wb in focus.

I assumed you knew what it was, I was just saying..

I had no idea it would change with focus... kindof destroys the point of having thisworkbook and activeworkbook, doesn't it? :rolleyes: MS mentality?

[RobDog888]

Yes, but think of it this way... When the workbook is running
ThisWorkbook code and you switch workbooks, the
ThisWorkbook class is changed to reflect the second workbooks
ThisWorkbook class. ThisWorkbook follows ActiveWorkbook.

[alkatran]

Originally posted by RobDog888
Yes, but think of it this way... When the workbook is running
ThisWorkbook code and you switch workbooks, the
ThisWorkbook class is changed to reflect the second workbooks
ThisWorkbook class. ThisWorkbook follows ActiveWorkbook.

So thisworkbook == activeworkbook??

I always though ThisWorkbook referred to the workbook the code was being run from. :confused:

[RobDog888]

No, I mean like the way that ActiveWorkbook changes from workbook
to workbook. So does ThisWorkbook changes from workbook to
workbook depending on which one is active.

[RobDog888]

If you have two workbooks open and each one has code in ThisWorkbook,
the only way Excel can tell which one it refers to is to check which
one is active.

Same thing with ActiveWorkbook, Excel know which workbook
ActiveWorkbook is by which one is currently active.