This will add a new "Budgeting menu to the worksheet menu bar and will be positioned just to the left of the Help menu:
Sub CreateMenu()
Dim HelpMenu As CommandBarControl
Dim NewMenu As CommandBarPopup
Dim MenuItem As CommandBarControl
Dim Submenuitem As CommandBarButton
' Delete the menu if it already exists
Call DeleteMenu
' Find the Help Menu
Set HelpMenu = CommandBars(1).FindControl(Id:=30010)
If HelpMenu Is Nothing Then
' Add the menu to the end
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
temporary:=True)
Else
' Add the menu before Help
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
Before:=HelpMenu.Index, _
temporary:=True)
End If
' Add a caption for the menu
NewMenu.Caption = "&Budgeting"
' FIRST MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Data Entry..."
.FaceId = 162
.OnAction = "Macro1"
End With
' SECOND MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Generate Reports..."
.FaceId = 590
.OnAction = "Macro2"
End With
' THIRD MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "View &Charts"
.BeginGroup = True
End With
' FIRST SUBMENU ITEM
Set Submenuitem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With Submenuitem
.Caption = "Monthly &Variance"
.FaceId = 420
.OnAction = "Macro3"
End With
' SECOND SUBMENU ITEM
Set Submenuitem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With Submenuitem
.Caption = "Year-To-Date &Summary"
.FaceId = 422
.OnAction = "Macro4"
End With
End Sub