Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem in defining OnAction macro definition held in Add-in!
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, _ Befo=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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy And Paste Macro Syntax Problem | Excel Worksheet Functions | |||
Solver Problem Definition | Excel Worksheet Functions | |||
Timing problem with OnAction! | Charts and Charting in Excel | |||
Problem with Button OnAction property... | Excel Programming | |||
Range definition problem | Excel Programming |