【Excel VBA】メニューバーに独自メニューを追加する

2022年10月27日2024年4月7日

当ページのリンクには広告が含まれている可能性があります。

こんにちは、ユーキです。

今回は、Excelのメニューバーに独自メニューを追加するやり方を紹介します。

メニューを追加する

メニューバーのオブジェクト取得

まず、メニューを追加するための、メニューバーのオブジェクトを取得します。

Dim menuBar As CommandBar
Set menuBar = Application.CommandBars("Worksheet Menu Bar")

変数名は何でもよいですが、「Application.CommandBars(“Worksheet Menu Bar”)」は定型句だと思ってください。

ボタンを追加

先ほど取得したメニューバーにボタンを追加します。

Dim menuBtn1 As CommandBarButton
Set menuBtn1 = menuBar.Controls.Add(Type:=msoControlButton)
'追加ボタンをテキスト形式となるよう設定
menuBtn1.Style = msoButtonCaption
menuBtn1.Caption = "テストボタン"

追加するボタンは、文字列にしたいので、StyleをmsoButtonCaptionに設定しています。
表示するテキストは、Captionで指定をします。

実行すると、以下のようになります。

サブメニューを追加

サブメニューにしたい場合の記述は以下のようになります。

'サブメニューを追加
Dim menuSbMenu As CommandBarPopup
Set menuSbMenu = menuBar.Controls.Add(Type:=msoControlPopup)
menuSbMenu.Caption = "yuuqyアドイン"
Dim sbMBtn1 As CommandBarButton
Set sbMBtn1 = menuSbMenu.Controls.Add(Type:=msoControlButton)
sbMBtn1.Style = msoButtonCaption
sbMBtn1.Caption = "サブボタン1"

サブメニューは、Typeが「msoControlPopup」になります。
サブメニューにメニューを追加するには、サブメニューに、ボタンを追加する形になります。

実行結果は以下の通りです。

追加したボタンに処理を割り当てる

処理を割り当てるには、OnActionプロパティを設定します。
さきほど、追加したサブボタン1に処理を指定します。指定するのは、呼び出すモジュール名になります。
モジュールは標準モジュールに定義をしてください。

sbMBtn1.OnAction = "ShowHello"
’以下は標準モジュールに記載をします。
Private Sub ShowHello()
    MsgBox "こんにちは"
End Sub

コード全行

ここまで書いた処理をまとめました。
これを全部行うと、ボタンとサブメニューが追加されます。

Dim menuBar As CommandBar
Set menuBar = Application.CommandBars("Worksheet Menu Bar")
’ボタンを追加
Dim menuBtn1 As CommandBarButton
Set menuBtn1 = menuBar.Controls.Add(Type:=msoControlButton)
'追加ボタンをテキスト形式となるよう設定
menuBtn1.Style = msoButtonCaption
menuBtn1.Caption = "テストボタン"
'サブメニューを追加
Dim menuSbMenu As CommandBarPopup
Set menuSbMenu = menuBar.Controls.Add(Type:=msoControlPopup)
menuSbMenu.Caption = "yuuqyアドイン"
Dim sbMBtn1 As CommandBarButton
Set sbMBtn1 = menuSbMenu.Controls.Add(Type:=msoControlButton)
sbMBtn1.Style = msoButtonCaption
sbMBtn1.Caption = "サブボタン1"
sbMBtn1.OnAction = "ShowHello"
’以下は標準モジュールに記載をします。
Private Sub ShowHello()
    MsgBox "こんにちは"
End Sub

実行結果は以下のようになります。

追加したメニュー、ボタンを削除する

追加したメニューを削除するやり方もみておきましょう。

コードで削除

コードで削除する場合、Deleteメソッドを使用します。

Dim menuBar As CommandBar
Set menuBar = Application.CommandBars("Worksheet Menu Bar")
menuBar.Controls("テストボタン").Delete
menuBar.Controls("yuuqyアドイン").Delete

全ボタンを削除してよい場合、コントロール名ではなく、インデックス指定で削除することも可能です。

Dim menuBar As CommandBar
Set menuBar = Application.CommandBars("Worksheet Menu Bar")
Dim i As Integer
For i = 1 To menuBar.Controls.Count
    ’繰り返しで要素が減っていくので、常に1番目の要素を削除
    menuBar.Controls(1).Delete
Next