C
Charles Jordan
Hi. I am using a modified version of John Walkenbach's outstanding
MenuMaker.xls to create a table driven Command bar structure.
(1) However the lookup table instead of being in the ActiveworkBook,
is in an add-in, in a worksheet, "Menusheet" in C1menus.xla, and the
cells in column 3 of this sheet each hold the names of the OnAction
macros.
(2) These macros however, which the menu items call, continue to be in
the ActiveworkBook, but when the menu item is invoked we get an error
message "The macro C1menus.xla!FixAll" cannot be found".
John Walkenbach's MenuMaker.xls code, slightly modified, in the add-in
is as follows:
Dim PositionOrMacro
Set MenuSheet = ThisWorkbook.Sheets("Menusheet") 'Location for menu
data
' Make sure the menus aren't duplicated
Call DeleteMenu
' Initialize the row counter
Row = 4
' Add the menus, menu items and submenu items using data stored on
MenuSheet
Do Until IsEmpty(MenuSheet.Cells(Row, 1)) 'stop when column 1 is
empty !
With MenuSheet
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
Select Case MenuLevel
Case 1 ' A Menu - Add the top-level menu to the Worksheet
CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption
Case 2 ' A Menu Item.
If NextLevel = 3 Then
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
'#######################
End If
and I suspect we need simplify to qualify the references to
PositionOrMacro in the line:-
MenuItem.OnAction = PositionOrMacro
with the word ActiveWorkbook, but the syntax has defeated us.
John - if you are there, can you help us, failing which any lesser
beings ?
Your help will be very greatly appreciated.
Charles
MenuMaker.xls to create a table driven Command bar structure.
(1) However the lookup table instead of being in the ActiveworkBook,
is in an add-in, in a worksheet, "Menusheet" in C1menus.xla, and the
cells in column 3 of this sheet each hold the names of the OnAction
macros.
(2) These macros however, which the menu items call, continue to be in
the ActiveworkBook, but when the menu item is invoked we get an error
message "The macro C1menus.xla!FixAll" cannot be found".
John Walkenbach's MenuMaker.xls code, slightly modified, in the add-in
is as follows:
Dim PositionOrMacro
Set MenuSheet = ThisWorkbook.Sheets("Menusheet") 'Location for menu
data
' Make sure the menus aren't duplicated
Call DeleteMenu
' Initialize the row counter
Row = 4
' Add the menus, menu items and submenu items using data stored on
MenuSheet
Do Until IsEmpty(MenuSheet.Cells(Row, 1)) 'stop when column 1 is
empty !
With MenuSheet
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
Select Case MenuLevel
Case 1 ' A Menu - Add the top-level menu to the Worksheet
CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption
Case 2 ' A Menu Item.
If NextLevel = 3 Then
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
'#######################
End If
and I suspect we need simplify to qualify the references to
PositionOrMacro in the line:-
MenuItem.OnAction = PositionOrMacro
with the word ActiveWorkbook, but the syntax has defeated us.
John - if you are there, can you help us, failing which any lesser
beings ?
Your help will be very greatly appreciated.
Charles