EXCEL菜单栏的添加删除

最新推荐文章于 2026-01-01 23:33:02 发布

原创最新推荐文章于 2026-01-01 23:33:02 发布 · 492 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #object #integer #search #menu #each

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

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