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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem in defining OnAction macro definition held in Add-in!
I am not sure wherein your code you get the error, but keep in mind
that an add in is never the ActiveWorkbook, if you need a reference to it, you should use Thisworkbook (where the code that is running resides) Rgsd Mika |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem in defining OnAction macro definition held in Add-in!
Charles,
To see the format you need, add a button from the forms toolbar to a spreadsheet. The choices of macros to assign that come up will be in the format you want. I suspect that you are missing two single-quotes and a "!" and that your new line will be: "'" & ActiveWorkbook.Name & "'!" & PositionOrMacro What I do is just put the full qualified name in the menusheet, e.g.,: 'MyBook.xls'!MyMacro hth, Doug Glancy "Charles Jordan" wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem in defining OnAction macro definition held in Add-in!
"Doug Glancy" wrote in message ...
Charles, To see the format you need, add a button from the forms toolbar to a spreadsheet. The choices of macros to assign that come up will be in the format you want. I suspect that you are missing two single-quotes and a "!" and that your new line will be: "'" & ActiveWorkbook.Name & "'!" & PositionOrMacro What I do is just put the full qualified name in the menusheet, e.g.,: 'MyBook.xls'!MyMacro hth, Doug Glancy Thanks a million Doug - just a little syntax problem..Charles "Charles Jordan" wrote in message om... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem in defining OnAction macro definition held in Add-in!
"Doug Glancy" wrote in message ...
Charles, To see the format you need, add a button from the forms toolbar to a spreadsheet. The choices of macros to assign that come up will be in the format you want. I suspect that you are missing two single-quotes and a "!" and that your new line will be: "'" & ActiveWorkbook.Name & "'!" & PositionOrMacro What I do is just put the full qualified name in the menusheet, e.g.,: 'MyBook.xls'!MyMacro hth, Doug Glancy Thanks a million Doug - just a little syntax problem..Charles "Charles Jordan" wrote in message om... 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 |
Reply |
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 |