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

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

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

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

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