Adding a menu to 2007

[Aussiebear]

I've been trying to follow the various posts regarding creating a custom menu. However everytime I read a new thread it seems that someone has a slightly different methodology. One thing I've noticed however is that any new menu ( when using 2007) is added into the Add-in section of the ribbon.

What I need is to be able to convert existing buttons into a menu item for a 2003 Excel version machine at work.

Apart from MD's contribution in the Nurofen thread, there was a much earlier thread (single post only) where the following contribution was offered. How effective is this?

[VBA]Const strMenuName As String = "&My Menu"
Const strCap1 As String = "First Caption"
Const strSub1 As String = "MySub1"
Const strCap2 As String = "Second Caption"
Const strSub2 As String = "MySub2"

Sub AddToWorksheetMB()
Dim cbWMB As CommandBar
Dim cbctrlCustom As CommandBarControl
Dim HelpMenu As CommandBarControl

DeleteFromWMB
Set cbWMB = Application.CommandBars(1)

On Error Resume Next
Set HelpMenu = cbWMB.FindControl (ID:30010)
On Error GoTo 0

If HelpMenu Is Nothing Then
Set cbctrlCustom = cbWMB.Controls.Add (Type:=msoControlPopup)
Else
Set cbctrlCustom = cbWMB.Controls.Add (Type:=msoControlPopup, before:=HelpMenu.Index)
End If

With cbctrlCustom
.Caption = strMenuName
With .Controls.Add (Type:=msoControlButton)
.Caption = strCap1
.OnAction = strSub1
End With
With .Controls.Add (Type:=msoControlButton)
.Caption = strCap2
.OnAction = strSub2
End With
End With

End Sub

Sub DeleteFromWMB()
Dim cbWMB As CommandBar
Set cbWMB = Application.CommandBars(1)
On Error Resume Next
cbWMB.Controls(strMenuName).Delete
End Sub

Sub MySub1()
MsgBox "Add 1 Code here"
End Sub

Sub MySub2()
MsgBox "Add 2 Code here"
End Sub

[/VBA]

[lucas]

Hi Ted,
I see you finally got started on this. I'm not sure where you got the code above...it looks like some that Malcolm put together and I think his intention was to make it easier to maintain and understand what was being done with the macros. It seems you copied one line incorrectly and I'm not sure how to fix it...can you give us a link to where you found it. This is the line that has a syntax error when I paste it into a module:
[VBA]Set HelpMenu = cbWMB.FindControl (ID:30010)[/VBA]

The example you have posted is fine and is a good example to learn from if that is your intent. If you are mostly interested in getting a reliable menu up and running I would still suggest that you experiment with the one from j-walk:
http://j-walk.com/ss/excel/tips/tip53.htm

[Bob Phillips]

Ted,

If you want a simple, maintainable way of adding menus, build a table driven menu builder. Walk has an example here at http://www.j-walk.com/ss/excel/tips/tip53.htm

2007 menus are a whole different fish. You need to create the menu in XML and bind that to a workbook.

[Aussiebear]

Hi Ted,
It seems you copied one line incorrectly and I'm not sure how to fix it...can you give us a link to where you found it. This is the line that has a syntax error when I paste it into a module:
[vba]Set HelpMenu = cbWMB.FindControl (ID:30010)[/vba]

Yes there is.... a typo by me I'm afraid. the line should read:

[vba]Set HelpMenu = cbMWB.FindControl (Id:= 30010)[/vba]

The example you have posted is fine and is a good example to learn from if that is your intent. If you are mostly interested in getting a reliable menu up and running I would still suggest that you experiment with the one from j-walk:
http://j-walk.com/ss/excel/tips/tip53.htm

I will try again tonight.

Edit: Hi Ted....I fixed your tags for you, hope that's ok.