PDA

View Full Version : Solved: Things That Happen When A Workbook Is Closed



Cyberdude
04-20-2008, 01:41 PM
At the end of a macro (belonging to Workbook A), I want to open Workbook B, then close the currently active Workbook A. As I understand it, when Workbook A is closed, then any statements that follow the "close" statement in a macro will NOT be executed.

However, if before closing Workbook A, I first open Workbook B, then Workbook B will become activated and subsequent macro statements will apply to Workbook B. I can then activate Workbook A and execute a "close" statement for Workbook A. At that point the macro in Workbook A will terminate, and only Workbook B will be active and no macros will be executing until I initiate execution of a macro belonging to Workbook B.

If I have a third workbook (Workbook C) open, but not active, when I close Workbook A, then I will have both Workbook B AND Workbook C open when Workbook A closes.

1. Which workbook (B or C) will become active when Workbook A closes?

2. How can I make sure that, say Workbook C, becomes active at that time?

tstav
04-21-2008, 12:19 AM
Hi Cyberdude,

I only tried it in a hurry and what I've come up with so far, is that trying to close A from within B's code, stops execution of code in B too (i.e. stops all code execution).
Maybe we should keep A open in the background after all (further experimenting might prove otherwise though...)

This is my test code and the way it runs is like this:
When A opens, it opens C and then activates A (so that C is in the background, if you want it so). Then a userform appears, with a button that says "Open B".
Pressing the button hides the A.userform and opens B.
When B opens, it comes of course to the foreground.
The code in B's Workbook_Open event activates C (what you asked for) and shows a new userform with a new button that says "Close A".
Pressing the button, closes A and that is where the code stops executing, plus the B.userform goes away by itself. Also, the msgbox I have added, does not show at all.

Regards, tstav
'A.xls
'''''''''''''''''''''''''''''''''''''
Private Sub Workbook_Open()
Workbooks.Open "C:\C.xls"
Workbooks("A.xls").Activate
UserForm1.Show
End Sub
'''''''''''''''''''''''''''''''''''''''''''
Private Sub btnOpenB_Click()
Me.Hide
Workbooks.Open "C:\B.xls"
End Sub

'B.xls
''''''''''''''''''''''''''''''''''''''
Private Sub Workbook_Open()
Workbooks("C.xls").Activate
UserForm1.Show
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub btnCloseA_Click()
Workbooks("A.xls").Close False
MsgBox "B is running, C is visible"
End Sub

Cyberdude
04-23-2008, 01:14 PM
Hey, thanks for the reply and effort!
Sid

Bob Phillips
04-23-2008, 02:57 PM
Do it all in another workbook, such as an addin, and specifically activate the desired workbook.

Cyberdude
04-23-2008, 07:46 PM
Cyberdude?s Encyclical on Closing Workbooks

Here are some observations regarding the effects of closing a workbook:

An executing VBA macro containing a statement that closes the currently active workbook will terminate the macro execution immediately after the ?close? statement executes successfully. Any macro statements following the ?close? statement will be ignored and will not be executed.

After the workbook closes, control will be given to the most recently previously-opened or activated workbook (if any). So one can say that, if a macro residing in Workbook ?A? activates already-opened (but not active) Workbook ?B?, then opens Workbook ?C?, then closes Workbook ?A?, Workbook ?C? will become the next active workbook since it was the most recently activated workbook.

These ?rules? suggest that the macro writer should be very cautious about placing any statements following a statement that closes the currently active workbook. This is particularly true about the placement of statements that re-enable Events or change Calculation back from Manual to Automatic. If these statements follow a ?close? statement, they will never be executed if the close statement executes.

To ensure that Workbook ?B? is activated after the closing of the currently active Workbook ?A?, the macro must activate Workbook ?B? before closing Workbook ?A?. Note that this activation must then be overridden by reactivating Workbook ?A? in order to execute the statement that closes it. After Workbook ?A? closes, control will pass to Workbook ?B?. No macros will be executing at that time, unless a workbook ?open? event executes a macro in Workbook ?B?.

Oorang
04-23-2008, 08:18 PM
Any macro statements following the “close” statement will be ignored and will not be executed. If application.enableevents is true (which it is by default). The code in or called by the WorkBookClose Event and ApplicationWorkbookClose event will still trigger. In addition if you close the workbook without supressing the save prompts, if the user does save when (if) prompted, the WorkbookBeforeSave event will trigger followed by the ApplicationWorkbookBeforeSave event.

And of course any events triggered by code in those events (or code called by those events) would all run as well.


Edit: Workbook deactive triggers too. Right after the save event(s)