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