Good morning.

I have a number of large data crunching routines ('children'), that because of their size and complexity, are too cumbersome for one workbook. To link them, I created a header workbook ('parent') that has instructions, common data lists, etc, and a custom menu that opens up each of the children workbooks. The custom menu reads the child's filepath from cells located on a page in the parent workbook.

This all works perfectly. Selecting a routine from the parent custom menu pulldown opens the child workbook, and the user is ready to go.

Many of these children routines have their own pulldowns to run specific items in each workbook. If I launch any of the children routines individually, the custom menu's in the children's workbooks loads and runs correctly. If I launch the children's routine from the parent, the file loads, but the child's custom menu is not added. However, If I manually run the child's custom menu loading routine it adds and activates the child's menu to the menu bar (along with all other custom menus).

Exiting any workbook deletes that workbook's custom menu and everything is back to normal.

So, how do I ensure the child's menu is always loaded and activated when I launch a child file from the parent menu? Shouldn't auto_open work whenever that workbook is opened and not the workbook that opens up the excel app?

Here's the code I'm running to add the menus: (Windows 7/ Excel 2007)

The Parent program code (can't seem to find the code formatting buttons):

Sub auto_open() ' Parent

Dim Cap(1)
Dim Mac(1)

Dim MenuName1 As String

MenuName1 = "Parent"

Cap(1) = "Children Workbooks"
Mac(1) = "LaunchChildrenWorkbook"

On Error Resume Next
' Delete the menu if it already exists
MenuBars(xlWorksheet).Menus(MenuName1).Delete

' Add the menu
MenuBars(xlWorksheet).Menus.Add Caption:=MenuName1

' Add the menu items
With MenuBars(xlWorksheet).Menus(MenuName1).MenuItems
.Add Caption:=Cap(1), OnAction:=Mac(1)
End With

End Sub

This opens up the child menu:

Sub auto_open() ' Child 1

Dim Cap(1)
Dim Mac(1)

Dim MenuName1 As String

MenuName1 = "Child 1"

Cap(1) = "This launches Child 1 routines"
Mac(1) = "Child1"

On Error Resume Next
' Delete the menu if it already exists
MenuBars(xlWorksheet).Menus(MenuName1).Delete

' Add the menu
MenuBars(xlWorksheet).Menus.Add Caption:=MenuName1

' Add the menu items
With MenuBars(xlWorksheet).Menus(MenuName1).MenuItems
.Add Caption:=Cap(1), OnAction:=Mac(1)
End With

End Sub


Art