Dosya düzen çubuğuna menü ekleme silme

Modüle

Option Explicit

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
    ' or 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 & "!Macroname"
    End With


    ' add menuitem to menu
    With cbMenu.Controls.Add(msoControlButton, 1, , , True)
        .Caption = "&Menu Item2"
        .OnAction = ThisWorkbook.Name & "!Macroname"
    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 & "!Macroname"
        .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 & "!Macroname"
        .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 & "!Macroname"
        .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 & "!Macroname"
        .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 Auto_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 ShowHideMenu(MenuVisible As Boolean)
' may be automatically executed from an Workbook_Activate macro or a Workbook_Deactivate eventmacro
    ChangeControlVisibility "MyTag", MenuVisible ' toggles menu visibility
End Sub

Private Sub ChangeControlVisibility(CustomControlTag As String, MenuVisible As Boolean)
' toggles menu visibility
    On Error Resume Next
    Application.CommandBars.FindControl(, , CustomControlTag, False).Visible = MenuVisible
    On Error GoTo 0
End Sub

Sub Macroname()
' used by the menuitems created by the CreateMenu macro
    MsgBox "This could be your macro running!", vbInformation, ThisWorkbook.Name
End Sub

'Thisworkbook a

Private Sub Workbook_Activate()
    ShowHideMenu True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveMenu
End Sub

Private Sub Workbook_Deactivate()
    ShowHideMenu False
End Sub

Private Sub Workbook_Open()
    CreateMenu
End Sub