custom menu cross contamination between open workbooks.

[windsurferLA]

I have several Excel workbooks that have custome menus. The workbooks
are set up so that the menus are generated when the workbooks are opened
and the menus are eliminated with the workbooks are closed. However, if
two workbooks are open at the same time, both sets of menus show on both
workbooks.

Some of the people that work for me end up running WorkbookA macros
(from menu) on WorkbookB, and vice versa. Would adding a test at the
beginning of each WorkbookA macro to make sure the active workbook is
WorkbookA prevent errors? If a Workbook is open but in a background
window or miniturized, is it still an "active workbook?"

Is there any way to prevent workbookA's menus from being visible in
WorkbookB if both are OPEN simultaneously?

The menu generation program was generously created by someone other than
myself, perhaps Joseph Rubin. I'm very much indebted to whoever wrote
the code, because it has made my workbooks a lot easier to use and
modify. The beginning of the code looks like:

Sub CreateMenu()
Dim MenuGen As Worksheet
Dim MenuObject As CommandBarPopup
Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, nextlevel, PositionOrMacro, Caption, Divider, FaceId

' Initialize the row counter
Row = 2
Do Until IsEmpty(MenuGen.Cells(Row, 1))
With MenuGen
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
nextlevel = .Cells(Row + 1, 1)
End With

Thanks WindsurferLA

 

[Darrin Henshaw]

I would use the Workbook_Activate event, to hide the custom menus, you
don't want shown. Just use an IF, or CASE statement, to hide the menus,
depending on which book is active.

 

[William]

Hi

2 suggestions:-

1) Use the events below in much the same way as the WorkBook_Open and
Workbook_Close events to delete / create your toolbars.....

Workbook_Activate()
Workbook_Deactivate()
Workbook_WindowActivate(ByVal Wn As Window)
Workbook_WindowDeactivate(ByVal Wn As Window)

2) If your macros have to be executed from a specific workbook, you could
start the code with something like...
If ActiveWorkbook.Name<>ThisWorkbook.Name then Exit Sub... etc. etc.

--

XL2003
Regards

William
(e-mail address removed)