workbook specific custom toolbars

[donwb]

Excel 2003
I have 3 workbooks each having a specific custom toolbar associated with
it..
The toolbars are very different from each other.
The workbooks are all open and used together, the switching from one to
another -
when required - being achieved using "Window" in the menu bar.
When this is done, my code automatically selects the "incoming" workbook's
associated toolbar OK and closes that of the "outgoing" book.
However, if I CLOSE (and save) any one of the WBs, say#1,
I want the next WB that appears (it doesn't matter which of the 2 left, say
#3)
to appear with it's correct associated bar.
The problem is I can't find a trigger event associated with the appearance
of WB#3 when WB#1 closes, to trigger the appropriate selection code.

By selecting a WB from the menu "Window", Private Sub Workbook_Activate()
is triggered, but when a WB just appears, simply because another has been
closed,
it doesn't.

My optimism says there must be some way of catching this change,
but so far it's escaped me!
Help please
donwb

 

[Bob Phillips]

How about the Workbook_Activate event?

What event are you using to close the other toolbar?

 

[donwb]

The Workbook_Activate event only seems to occur when I use menu "Window" to
select a WB.
I use that to show the appropriate TB and Workbook_Deactivate to close the
other TB.
These events don't seem to be triggered when a WB "appears" following the
CLOSURE
of another.

 

[Bob Phillips]

This statement surprised me ... These events don't seem to be triggered
when a WB "appears" following the CLOSURE of another... so I tried it, and
it triggered Activate for me.

 

[donwb]

Hi Bob

By way of a simple test, I created two WBs, Test1 & Test2.
Test1 has the code:-
Sub MyClose()
ThisWorkbook.Close SaveChanges:=False
End Sub
located in "ThisWorkbook"

Test2 has the code:-
Private Sub Workbook_Activate()
MsgBox "I'm activated"
End Sub
also located in "ThisWorkbook"

With both WBs open, if I run the code in Test1 to close it
an error message appears:-
"Application-defined or object-defined error",
WB1 closes, but the MsgBox never appears.

However, if I close WB1 using instead the menu bar user interface
File/Close,
Activate is triggered and the MsgBox appears.

I want to do the closing programmatically so I'm still stuck.
donwb