PDA

View Full Version : Solved: Adding New Menu



SJ McAbney
08-23-2004, 03:58 AM
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
08-23-2004, 04:21 AM
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)
08-23-2004, 04:22 AM
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 SubAny problems - let me know. ;)

SJ McAbney
08-23-2004, 04:24 AM
Perfect. Thanks.

SJ McAbney
08-23-2004, 04:29 AM
Incidentally, how would you add a child menu?
And delete the thing?

Richie(UK)
08-23-2004, 05:27 AM
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 SubHTH

SJ McAbney
08-23-2004, 05:45 AM
Excellent. I appreciate this.