Office TANAKA - Excel VBA Tips[メニューバーに独自のメニューを追加する]

VBA でシステムを作ったとき、機能をメニューから選択できると便利ですね。独自のメニューをメニューバーに登録する方法をご紹介しましょう。

Sub AddMenu()
    Dim NewM As Variant, NewC As Variant
    ''新しいメニューを追加する
    Set NewM = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
    NewM.Caption = "新しいメニュー(&C)"
    ''オリジナルコマンドを追加する(1)
    Set NewC = NewM.Controls.Add
    With NewC
        .Caption = "保護解除(&U)"
        .OnAction = "UnProtectSheet"
        .BeginGroup = False
        .FaceId = 277
    End With
    ''オリジナルコマンドを追加する(2)
    Set NewC = NewM.Controls.Add
    With NewC
        .Caption = "参照元/先のトレース(&P)"
        .OnAction = "Precedents"
        .BeginGroup = True
        .FaceId = 450
    End With
End Sub

Excel のメニューバーは「Worksheet Menu Bar」という名前の CommandBar オブジェクトです。Add メソッドでコントロールを追加できます。引数 Type に msoControlPopup を指定すると、サブメニュー項目を持つメニューコマンドが追加されます。

変数 NewM に新しく追加したメニューが格納されていますので、メニューにコマンドを追加するには「NewM.Controls.Add」とします。クリックすると実行される一般的なコマンドを追加するときは、引数 Type を指定する必要はありません。

追加したコマンドに表示する名前は「Caption プロパティ」に設定します。設定している文字列中に「&」とアルファベットを 1 文字指定することショートカットキーを設定できます。たとえば「新しいメニュー(&C)」のメニューは、Alt キーと「C」キーを押すことでアクセスできます。「OnAction プロパティ」はクリックされたときに実行するプロシージャです。無用なトラブルを避けるためにも、特別な理由がなければ標準プロシージャに書いた方がいいでしょう。「FaceId プロパティ」はアイコンです。数字で指定します。Excel 用の FaceID 一覧でしたら持っていますので、ご覧になりたい方はメールしてください。「BeginGroup プロパティ」に True を設定すると、そのコマンドの直前に区切り線が表示されます。

通常のコマンドではなくサブコマンドを持つサブメニューを追加することもできます。Excel の標準メニュー [データ] - [フィルタ] みたいなヤツです。これには「NewM.Controls.Add」の Add メソッドに引数 Type を指定します。

Sub AddMenu()
    Dim NewM As Variant, NewC As Variant
    ''新しいメニューを追加する
    Set NewM = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
    NewM.Caption = "新しいメニュー(&C)"
    ''オリジナルコマンドを追加する(1)
    Set NewC = NewM.Controls.Add
    With NewC
        .Caption = "保護解除(&U)"
        .OnAction = "UnProtectSheet"
        .BeginGroup = False
        .FaceId = 277
    End With
    ''オリジナルコマンドを追加する(2)
    Set NewC = NewM.Controls.Add
    With NewC
        .Caption = "参照元/先のトレース(&P)"
        .OnAction = "Precedents"
        .BeginGroup = True
        .FaceId = 450
    End With
    ''オリジナル サブメニューを追加する
    Set NewC = NewM.Controls.Add(Type:=msoControlPopup)
    With NewC
        .Caption = "標準に戻す"
        .BeginGroup = True
        With NewC.Controls.Add()
            .Caption = "シートの外観(&S)"
            .OnAction = "SheetStyle"
            .FaceId = 8
        End With
    ''オリジナル サブメニューのコマンドを追加する
        With NewC.Controls.Add()
            .Caption = "文字色(&T)"
            .OnAction = "DefaultFontColor"
            .FaceId = 476
        End With
    End With
End Sub

このようなオリジナルメニューは、標準のメニューバーだけでなくツールバーに追加することもできます。

Set NewM = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)

の「Worksheet Menu Bar」に追加したいメニューバーの名前を指定すれば OK です。なお、ツールバーにテキストボックスやコンボボックスを追加するテクニックは「ツールバーでテキストボックスを使う」を参照してください。