Excel VBA 右クリックメニューにマクロ実行メニューを追加する

右クリックメニューにマクロ実行メニューを追加する方法

Excelになれていない人にマクロを実行してもらう場合右クリックメニューにマクロ実行メニューがあると扱いやすくなると思います。

VBAでは「メニューバー」「ツールバー」「ショートカットメニュー」を総称して「コマンドバー」といいます。そのコマンドバーを制御するには、コマンドバーを表すCommanBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトを使用します。CommanBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトはCommandBars【ｺﾏﾝﾄﾞﾊﾞｰｽﾞ】プロパティを使用して参照します。

CommandBars【ｺﾏﾝﾄﾞﾊﾞｰｽﾞ】プロパティ

コマンドバーを表すCommanBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトを取得するには、Application【ｱﾌﾟﾘｹｰｼｮﾝ】オブジェクトのCommandBars【ｺﾏﾝﾄﾞﾊﾞｰｽﾞ】プロパティを使用します。

CommandBars【ｺﾏﾝﾄﾞﾊﾞｰｽﾞ】プロパティの書式の書式と設定値(引数)の説明

[]内は省略可能です。
[オブジェクト.]CommandBars[(コマンドバー名)]
【戻り値】CommanBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクト

Controls【ｺﾝﾄﾛｰﾙｽﾞ】プロパティ

VBAでは、コマンドバーに配置されているメニューやボタンなどの各要素をCommandBarControl【ｺﾏﾝﾄﾞﾊﾞｰｺﾝﾄﾛｰﾙ】オブジェクトとして扱います。そのCommandBarControl【ｺﾏﾝﾄﾞﾊﾞｰｺﾝﾄﾛｰﾙ】オブジェクトを参照するにはCommandBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトのControls【ｺﾝﾄﾛｰﾙｽﾞ】プロパティプロパティを使用します。

Controls【ｺﾝﾄﾛｰﾙｽﾞ】プロパティの書式と設定値の説明

オブジェクト.Controls
【戻り値】CommandBarControl【ｺﾏﾝﾄﾞﾊﾞｰｺﾝﾄﾛｰﾙ】オブジェクト

右クリックショートカットメニューにメニューを追加するコードと解説

Sub コマンド追加()
  With CommandBars("cell").Controls.Add
      .Caption = "マクロ実行"
End With
End Sub

実行結果

※注意しなくてはいけないことは、このメニュー追加は、
このWorkbookだけではなくて、すべてのWorkbookに適用されます。

追加した右クリックショートカットメニューを削除するコード

Sub コマンド削除()
  CommandBars("cell").Controls("マクロ実行").Delete
End Sub

いつでも、コマンドを削除できるように、同じモジュール内にコマンド削除のコードも記述しておきましょう

コマンドの登録位置を指定する方法

実行結果からわかるように、追加したコマンドはメニューの一番下に追加されます。コマンドを追加するAdd(ｱﾄﾞ)メソッドでは、追加する位置を指定できます。引数Before【ﾋﾞﾌｫｱ】に番号を指定すると、指定した番号のメニューの上にコマンドが追加されます。

メニューの一番上にコマンドを追加するコード

Sub コマンド追加()
  With CommandBars("cell").Controls.Add(Before:=1)
      .Caption = "マクロ実行"
End With
End Sub

実行結果

コマンドにサブメニューを追加する

追加したコマンドにサブメニューを追加することもできます。サブメニューを登録するには、Add【ｱﾄﾞ】メソッドの引数Typeに定数msoControlPopup(ｴﾑｴｽｵｰｺﾝﾄﾛｰﾙﾎﾟｯﾌﾟｱｯﾌﾟ)を指定します。こうして登録したコントロールは、サブメニューになるので
そのサブメニューに新たなコマンドを登録します。

サブメニュー付きのコマンド追加コード

Sub コマンド追加()
  With CommandBars("cell").Controls.Add(Before:=1, Type:=msoControlPopup)
       .Caption = "マクロ実行"
  With .Controls.Add
       .Caption = "マクロ1"
End With
  With .Controls.Add
       .Caption = "マクロ2"
End With
End With
End Sub

実行結果

コマンドにマクロを設定する方法

追加したコマンドにマクロを設定するにはOnAction(ｵﾝｱｸｼｮﾝ)プロパティに実行したいプロシージャ―の名前を登録します。

追加したサブコマンドにプロシージャ呼び出しを設定したコード

Sub コマンド追加()
  With CommandBars("cell").Controls.Add(Before:=1, Type:=msoControlPopup)
      .Caption = "マクロ実行"
  With .Controls.Add
       .Caption = "マクロ1"
       .OnAction = "マクロ1"
End With
  With .Controls.Add
       .Caption = "マクロ2"
       .OnAction = "マクロ2"
End With
End With
End Sub

設定したプロシージャ　マクロ1とマクロ2のコード

Sub マクロ1()
   MsgBox "マクロ1"
End Sub

Sub マクロ2()
   MsgBox "マクロ2"
End Sub

プロシージャは実行されるとメッセージBOXにマクロ1　マクロ2と表示されます。同じモジュール内に記述しました。

実行結果

以上で、右クリックメニューにマクロ実行メニューを追加する方法についての解説を終了します。ありがとうございました。