EXCEL菜单栏的添加删除_excel マクロ-优快云博客

本文链接：https://blog.youkuaiyun.com/yanjun_xiaoli/article/details/4037962

本文介绍了一种使用VBA为Excel工作簿自定义菜单栏的方法。通过在Workbook_Open及Workbook_BeforeClose事件中添加代码，可以实现在打开工作簿时显示包含特定宏命令的自定义菜单，并在关闭工作簿前清除这些自定义项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Private Sub Workbook_Open()
    Dim TargetBar   As CommandBar
    Dim NewMenu     As Object
    Dim NewMenu0     As Object
    Dim NewItem1     As Object
    Dim NewItem2    As Object
    Dim NewItem3    As Object
    Dim NewItem4     As Object
    Dim LoopVar     As Variant
    Dim Stat        As Integer
    Dim NewSubItem As Object

Set TargetBar = Application.CommandBars("Worksheet Menu Bar")
TargetBar.Visible = True

Set NewMenu = TargetBar.Controls.Add(Type:=msoControlPopup, ID:=1, temporary:=True)
NewMenu.Caption = "マクロ展開ツール"

    Set NewItem1 = NewMenu.Controls.Add(Type:=msoControlButton, ID:=2, temporary:=True)
    Set NewItem4 = NewMenu.Controls.Add(Type:=msoControlButton, ID:=1, temporary:=True)
    NewItem1.Caption = "マクロ一覧取得"
    NewItem1.OnAction = "search_MACRO"
    NewItem4.Caption = "表紙作成"
    NewItem4.OnAction = "search_MACRO2"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim TargetBar   As CommandBar
    Dim LoopVar     As Variant
    Dim Stat        As Integer

On Error Resume Next

Set TargetBar = Application.CommandBars("Worksheet Menu Bar")

    Stat = 0
    With TargetBar
        For Each LoopVar In .Controls
            If (LoopVar.Caption = "マクロ展開ツール") Then
                Stat = 1
                TargetBar.Controls(LoopVar.Index).Delete
                Exit For
            End If
        Next LoopVar
    End With
    Set TargetBar = Nothing
End Sub