Aussiebear
10-21-2007, 04:33 AM
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?
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
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?
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