Option Explicit
Sub CreateMenu()
Dim cbMenu As CommandBarControl, cbSubMenu As CommandBarControl
RemoveMenu ' delete the menu if it already exists
' create a new menu on an existing commandbar
Set cbMenu = Application.CommandBars(1).Controls.Add(msoControlPopup, , , , True)
With cbMenu
.Caption = "&Menu title"
.Tag = "MyTag"
.BeginGroup = False
End With
If cbMenu Is Nothing Then Exit Sub ' didn't find the menu...
Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = "&First sub menu"
.Tag = "SubMenu1"
.BeginGroup = True
End With
' add menuitem to submenu
With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&First action"
.OnAction = "macro1"
.Style = msoButtonIconAndCaption
.FaceId = 71
.State = msoButtonDown ' or msoButtonUp
End With
' add menuitem to submenu
With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Second action"
.OnAction = "macro2"
.Style = msoButtonIconAndCaption
.FaceId = 72
.Enabled = True '
End With
Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = "&second submenu"
.Tag = "Macro3"
.BeginGroup = True
End With
'add a submenu
With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
'With cbSubMenu
.Caption = "&etc"
.Tag = "SubMenu1"
.OnAction = "etc"
.BeginGroup = True
End With
' add menuitem to submenu
With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&etc"
.OnAction = "etc"
.Style = msoButtonIconAndCaption
.FaceId = 71
.State = msoButtonUp '
End With
' add menuitem to submenu
With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&etc"
.OnAction = "etc"
.Style = msoButtonIconAndCaption
.FaceId = 71
.State = msoButtonUp '
End With
' add menuitem to submenu
With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&etc"
.OnAction = "etc"
.Style = msoButtonIconAndCaption
.FaceId = 71
.State = msoButtonUp '
End With
Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = "&etc"
.Tag = "SubMenu1"
.BeginGroup = True
End With
' add menuitem to submenu
With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&etc"
.OnAction = "etc"
.Style = msoButtonIconAndCaption
.FaceId = 71
.State = msoButtonUp '
End With
' add menuitem to submenu
With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&etc"
.OnAction = "etc"
.Style = msoButtonIconAndCaption
.FaceId = 71
.State = msoButtonUp '
End With
' add menuitem to submenu
'With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
' .Caption = "&etc"
'.OnAction = "etc"
'.Style = msoButtonIconAndCaption
'.FaceId = 72
'.Enabled = True '
' End With
' add menuitem to submenu
End Sub
Sub RemoveMenu()
DeleteCustomCommandBarControl "MyTag" ' deletes the new menu
End Sub
Private Sub DeleteCustomCommandBarControl(CustomControlTag As String)
' deletes ALL occurences of commandbar controls with a tag = CustomControlTag
On Error Resume Next
Do
Application.CommandBars.FindControl(, , CustomControlTag, False).Delete
Loop Until _
Application.CommandBars.FindControl(, , CustomControlTag, False) Is Nothing
On Error GoTo 0
End Sub
Bookmarks