Problem with Workbook_Activate event

[donwb]

I'm using Excel 2003 under Win XP

I have several *.xls WorkBooks open under the same instance of the Excel
Application.

Each has a unique associated custom toolbar (CommandBar) which is made
visible by the following code located in ThisWorkbook/ Microsoft Excel
Objects



Private Sub Workbook_Activate()

Application.CommandBars("MyBook1").Visible = True

End Sub



In the same ThisWorkbook location is also the following code.

Private Sub Workbook_Deactivate()

Application.CommandBars("MyBook1").Visible = False

End Sub



In the simple case, with only two WorkBooks open and book#2, say, visible,
if I select Window on the menu bar and select book#1, then book#1 becomes
visible and so does its associated tool bar, while book#2 is no longer
visible nor, importantly is its associated ToolBar. I can toggle between the
2 books and every time, the correct ToolBar for the visible book is visible.

This operation is correct and as required.



The problem comes when either book is closed.

Under this situation, if say book#1 is closed using the following code:-



Workbooks("MyBook1.xls").Close SaveChanges:=False



I had expected this to trigger the "Private Sub Workbook_Activate()" event
of the remaining workbook as it becomes visible, but it doesn't; the event
for some reason, is not triggered.



I need something which triggers when this remaining book appears,

or in the more complex situation of several books open and one being closed,

something which triggers when the next book "in the line-up" becomes
visible.

Any suggestions gratefully accepted.

donwb

 

[Joel]

Rather than use Application use Thisworkbook. Thisworkbook will ALWAYS refer
to the wrorkbook with where the Macro is running and won't have problems if
you open other workbooks.

When you use more than one workbook always use thisworkbook and set a
seperate variables to the other workbooks to minimize errors. The problem
with excel is when you open a workbook or add worksheets the focus changes
and frequently causes errors.

ThisWorkbook.CommandBars("MyBook1").Visible = False

 

[donwb]

Hi Joel
Many thanks for your input.
Yes I see the logic of your suggestion.
I replaced Application with ThisWorkBook,
but get an error message "object variable or With Block variable not set."
Should the revised code still be located in
Microsoft Excel Objects / ThisWorkbook,
or should it now go in a separate module under Modules?
donwb

 

[Joel]

I'm alwaays learning new things. I added commands bar manually from the
spreadsheet, but not through a program. the toolbars and menues are not
associated with a workbook, they are part of the excel application. Your
toolbar commands don't need the word application. commands working with
workbooks such SAVEAS can use Thisworkbook.