C
c1802362
Good morning.
I have a number of large data crunching routines ('children'), that becauseof their size and complexity, are too cumbersome for one workbook. To linkthem, I created a header workbook ('parent') that has instructions, commondata 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 wheneverthat 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
I have a number of large data crunching routines ('children'), that becauseof their size and complexity, are too cumbersome for one workbook. To linkthem, I created a header workbook ('parent') that has instructions, commondata 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 wheneverthat 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