Public Sub AddMenuItemExample()
Dim cbWSMenuBar As CommandBar
Dim cbc As CommandBarControl
Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar")
Set cbc = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
cbc.Tag = "MyMenu"
With cbc
.Caption = "&My Menu"
With .Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Item &1"
.OnAction = "ThisWorkbook.SayHello"
.Tag = "Item1"
End With
With .Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Item &2"
.OnAction = "ThisWorkbook.SayHello"
.Tag = "Item2"
End With
With .Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Item &3"
.OnAction = "ThisWorkbook.SayHello"
.Tag = "Item 3"
End With
With .Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Item &4"
.OnAction = "ThisWorkbook.SayHello"
.BeginGroup = True
.Tag = "Item4"
End With
With .Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Item &5"
.OnAction = "ThisWorkbook.SayHello"
.Tag = "Item5"
.BeginGroup = True
End With
With .Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Item &6"
.OnAction = "ThisWorkbook.SayHello"
.Tag = "Item6"
End With
End With
End Sub
Private Sub SayHello()
MsgBox "Hello", vbOKOnly
End Sub