Adding New Menu

[SJ McAbney]

How do I add a new menu after the Help menu?

I can add to other menus but my attempts thus far at creating my own menu have been fruitless.

[Daniel Klann]

Hi,

Do you mean through code?


Sub AddMenu()
Dim ComPop As CommandBarPopup
Dim Combut As CommandBarButton
Set ComPop = Application.CommandBars("Worksheet Menu Bar").Controls.Add(msoControlPopup)
ComPop.Caption = "My New Menu"
Set Combut = ComPop.Controls.Add(msoControlButton)
Combut.Style = msoButtonCaption
Combut.Caption = "Button 1"
Combut.OnAction = "MyMacro"
Set Combut = ComPop.Controls.Add(msoControlButton)
Combut.Style = msoButtonCaption
Combut.Caption = "Button 2"
Combut.OnAction = "MyMacro"
End Sub

Sub MyMacro()
MsgBox "You pressed " & Application.CommandBars.ActionControl.Caption
End Sub


HTH
Dan

[Richie(UK)]

Hi SJ,

See if this helps:


Const strMenuName As String = "&My Menu"
Const strCap1 As String = "Cap1"
Const strSub1 As String = "Sub1"

Sub AddToWorksheetMB()
Dim cbWMB As CommandBar
Dim cbctrlCustom As CommandBarControl
Dim HelpMenu As CommandBarControl
DeleteFromWMB
Set cbWMB = Application.CommandBars("Worksheet Menu Bar")
On Error Resume Next
Set HelpMenu = CommandBars(1).FindControl(Id:=30010)
On Error GoTo 0
If HelpMenu Is Nothing Then
Set cbctrlCustom = cbWMB.Controls.Add(Type:=msoControlPopup)
'Help doesn't exist so add to the end
Else
Set cbctrlCustom = cbWMB.Controls.Add(Type:=msoControlPopup, before:=HelpMenu.Index + 1)
'Add after Help
End If
With cbctrlCustom
.Caption = strMenuName
With .Controls.Add(Type:=msoControlButton)
.Caption = strCap1
.OnAction = strSub1
End With
'add a menu item
End With
End Sub

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

Sub Sub1()
MsgBox "Hello"
End Sub

Any problems - let me know. ;)

[SJ McAbney]

Perfect. Thanks.

[SJ McAbney]

Incidentally, how would you add a child menu?
And delete the thing?

[Richie(UK)]

Hi SJ,

OK, version2 adds a pop-up menu too. (The deletion routine was already in the original - "DeleteFromWMB"). Is this what you wanted?


Const strMenuName As String = "&My Menu"
Const strCap1 As String = "Cap1"
Const strSub1 As String = "Sub1"
Const strCap2 As String = "Cap2"
Const strSub2 As String = "Sub2"

Sub AddToWorksheetMB()
Dim cbWMB As CommandBar
Dim cbctrlCustom As CommandBarControl, cbPop As CommandBarPopup
Dim HelpMenu As CommandBarControl
DeleteFromWMB
Set cbWMB = Application.CommandBars("Worksheet Menu Bar")
On Error Resume Next
Set HelpMenu = CommandBars(1).FindControl(Id:=30010)
On Error GoTo 0
If HelpMenu Is Nothing Then
Set cbctrlCustom = cbWMB.Controls.Add(Type:=msoControlPopup)
'Help doesn't exist so add to the end
Else
Set cbctrlCustom = cbWMB.Controls.Add(Type:=msoControlPopup, before:=HelpMenu.Index + 1)
'Add after Help
End If
With cbctrlCustom
.Caption = strMenuName
With .Controls.Add(Type:=msoControlButton)
.Caption = strCap1
.OnAction = strSub1
End With
'add a menu item
Set cbPop = .Controls.Add(msoControlPopup)
With cbPop
.BeginGroup = True
.Caption = "Pop menu :"
.Tag = "MyPop"
With cbPop.Controls.Add(Type:=msoControlButton)
.Style = msoButtonCaption
.Caption = strCap2
.OnAction = strSub2
End With
End With
'popup on main popup
End With
End Sub

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

Sub Sub1()
MsgBox "Hello"
End Sub

Sub Sub2()
MsgBox "Hello again!"
End Sub

HTH

[SJ McAbney]

Excellent. I appreciate this.