Add-ins tab of the Ribbon

Sub CreateMenu()

' creates a new menu.

' can also be used to create commandbarbuttons

' may be automatically executed from an Auto_Open macro or a Workbook_Open eventmacro

Dim cbMenu As CommandBarControl, cbSubMenu As CommandBarControl

RemoveMenu ' delete the menu if it already exists

' create a new menu on an existing commandbar (the next 6 lines)

Set cbMenu = Application.CommandBars(1).Controls.Add(msoControlPopup, , , , True)

With cbMenu

.Caption = "&My menu"

.Tag = "MyTag"

.BeginGroup = False

End With

' alternatively, add to an existing menu (use the next line instead of the previous 6 lines)

'Set cbMenu = Application.CommandBars.FindControl(, 30007) ' Tools-menu

If cbMenu Is Nothing Then Exit Sub ' didn't find the menu...

' add menuitem to menu

With cbMenu.Controls.Add(msoControlButton, 1, , , True)

.Caption = "&Menu Item1"

.OnAction = ThisWorkbook.Name & "!TestMacro"

End With

' add menuitem to menu

With cbMenu.Controls.Add(msoControlButton, 1, , , True)

.Caption = "&Menu Item2"

.OnAction = ThisWorkbook.Name & "!TestMacro"

End With

' add a submenu

Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)

With cbSubMenu

.Caption = "&Submenu1"

.Tag = "SubMenu1"

.BeginGroup = True

End With

' add menuitem to submenu (or buttons to a commandbar)

With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)

.Caption = "&Submenu Item1"

.OnAction = ThisWorkbook.Name & "!TestMacro"

.Style = msoButtonIconAndCaption

.FaceId = 71

.State = msoButtonDown ' or msoButtonUp

End With

' add menuitem to submenu (or buttons to a commandbar)

With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)

.Caption = "&Submenu Item2"

.OnAction = ThisWorkbook.Name & "!TestMacro"

.Style = msoButtonIconAndCaption

.FaceId = 72

.Enabled = False ' or True

End With

' add a submenu to the submenu

Set cbSubMenu = cbSubMenu.Controls.Add(msoControlPopup, 1, , , True)

With cbSubMenu

.Caption = "&Submenu2"

.Tag = "SubMenu2"

.BeginGroup = True

End With

' add menuitem to submenu submenu

With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)

.Caption = "&Submenu Item1"

.OnAction = ThisWorkbook.Name & "!TestMacro"

.Style = msoButtonIconAndCaption

.FaceId = 71

.State = msoButtonDown ' or msoButtonUp

End With

' add menuitem to submenu submenu

With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)

.Caption = "&Submenu Item2"

.OnAction = ThisWorkbook.Name & "!TestMacro"

.Style = msoButtonIconAndCaption

.FaceId = 72

.Enabled = False ' or True

End With

' add menuitem to menu

With cbMenu.Controls.Add(msoControlButton, 1, , , True)

.Caption = "&Remove this menu"

.OnAction = ThisWorkbook.Name & "!RemoveMenu"

.Style = msoButtonIconAndCaption

.FaceId = 463

.BeginGroup = True

End With

Set cbSubMenu = Nothing

Set cbMenu = Nothing

End Sub

Sub RemoveMenu()

' may be automatically executed from an Workbook_Close macro or

' a Workbook_BeforeClose eventmacro

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

Sub TestMacro()

' used by the menuitems created by the CreateMenu macro

MsgBox "This would run your macro!", vbInformation, ThisWorkbook.Name

End Sub