右クリックメニューやサブメニューにマクロを登録／削除するには：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、CommandBars("Cell")のAddメソッドを使い自作したマクロをメニューに登録して呼び出す方法や、Deleteメソッドでメニューから削除する方法などを解説します。

[薬師寺国安，PROJECT KySS]

連載目次

※本Tipsの環境：Windows 8.1 Enterprize（64ビット）＋Excel 2013

VBA／マクロを使えば、メニューも自由に追加できる

　Tips「Excelのメニューに自作したマクロを追加するには」で、Excelの上部にあるメニューバーにメニューの一つとして自作マクロを登録する方法を解説した。

　今回は、右クリックメニューにコマンドを登録してマクロと関連付ける方法と、さらにそのサブメニューにもマクロを登録する方法を解説する。今回呼び出すマクロとしては、文字を呼び出す簡単なものだが、もちろん、呼び出すマクロは自由なので、各自作成したマクロを呼び出してほしい。

　なお、登録したメニューを削除するマクロも紹介しておく。試しに登録してみたメニューを消すこともできるので、失敗を恐れずに挑戦してほしい。

右クリックメニューにコマンドを登録してマクロと関連付ける

　右クリックメニューにコマンドを登録してマクロと関連付けるには以下の書式を使う。Tips「Excelのメニューに自作したマクロを追加するには」とほぼ同様だが、CommandBarsオブジェクトで指定する引数が「"Worksheet Menu Bar"」ではなく「"Cell"」になっていることに注目してほしい。こうすることで、CommandBarsは右クリックメニューのオブジェクトになる。

右クリックメニューにコマンドを登録してマクロと関連付けるCommandBars("Cell").Addの書式

With CommandBars("Cell").Controls.Add(Before:=｛メニューを表示する位置｝）

　.Caption="｛メニューに表示する名前｝"

　.OnAction="｛実行するマクロのメソッド名｝"

End With

　Captionプロパティでは、｛メニューに表示する名前｝を指定し、OnActionプロパティには、｛実行するマクロのメソッド名｝を指定する。

　この書式を基にコード例を示していこう。今回は「メニューコマンドを登録してマクロと関連付ける」シートを2つ目のシートとして追加しておき、Module1内ではなく「Sheets2（メニューにコマンドを登録してマクロと関連付ける）」内に、リスト1のコードを書いていくことにする。

Option Explicit
Private Sub Worksheet_Activate()
  Application.CommandBars("Cell").Reset
  With CommandBars("Cell").Controls.Add(Before:=1)
    .Caption = "文字を表示"
    .OnAction = "文字を表示する"
  End With
End Sub

リスト1　「右クリックメニューにコマンドを登録する」コード

　Worksheetがアクティブになったときの、Worksheet_Activateイベント内に記述する。このWorksheet_Activateは図1の方法で自動的にコードが挿入される。

図1　Worksheet_Activateイベントを表示する

　リスト1の中身を解説しよう。まず3行目の「Application.CommandBars("cell").Reset」でメニューの内容をリセットしておく。この処理を書いていないと、シートがアクティブになるたびに、「文字を表示」という文字がメニューに重複して表示されてしまうので注意が必要だ。

　4行目では、「Add(Before:=1)」と記述して、メニューの一番先頭にメニューを追加するようにしている。

　5行目でCaptionプロパティに「文字を表示」を、6行目でOnActionプロパティにCaptionプロパティにクリックしたときに実行される処理を指定する。

　OnActionプロパティには、「文字を表示する」と指定しているので、この処理を記述する必要がある。この「文字を表示する」処理を、同じ、「Sheets2(メニューにコマンドを追加してマクロと関連付ける)」内に記述すると、「マクロが無効になっている〜」のメッセージが出て実行できない。

　このOnActionプロパティに指定した「文字を表示する」処理は、Module1内に記述する必要があるのだ。このことを書いてある書籍は意外と少ない。この点に注意してもらいたい。

　Module1内に、リスト2の「文字を表示する」コードを記述する。

Sub 文字を表示する()
  Range("B2").Value = "Excel VBA Tips集"
  With Range("B2").Font
    .Name = "Meiryo UI"
    .Size = 40
    .ColorIndex = 7
    .Bold = True
  End With
End Sub

リスト2　「文字を表示する」コード

　このマクロはセル「B2」に、「Excel VBA Tips集」と表示し、書体に「Meiryo UI」、文字サイズに「40」、文字色に「7（ピンク系統色）」、「太字」と指定する簡単なものだ。

　「メニューコマンドを登録してマクロと関連付ける」シートがアクティブになったときに、セル上でマウスの右クリックをすると、メニューの先頭に「文字を表示」と表示される（図2）。

図2　マウスの右クリックで表示されたメニュー

　これをクリックすると、セル「B2」に「Excel VBA Tips集」と文字が表示される（図3）。

図3　「文字を表示」をクリックすると、文字が表示された

メニューからコマンドを削除する

　なお、登録したコマンドを削除する場合は下記のように記述する。

メニューからコマンドを削除するCommandBars.Controls.Deleteメソッドの書式

CommandBars("Cell").Controls(｛Captionプロパティに指定したメニュー名｝).Delete

　この場合は、必ずマウスを右クリックして表示されるメニューに、｛Captionプロパティに指定したメニュー名が登録されている必要がある。そうでないとエラーになるので注意してほしい。

複数のサブメニューを追加する

　続いて、「複数のサブメニューを追加する」方法について解説する。

サブメニューを追加する書式

CommandBars("Cell").Controls.Add(Before:=｛メニューを表示する位置｝,Type:=msoControlPopu)

　引数「Before」には、メニューを表示する位置を数値で指定する。「1」を指定すると、一番先頭に表示される。「Type」には、定数「msoControlPopu」を指定する。この配下に、サブメニューとなる内容を追加していくことになる。

　他に設定することはないので、早速、実際にマクロを記述してみよう。

　今回はModule1内ではなく、Sheet1（Sheet1）内にリスト3のコードを書いていく。

Sheet1内のWorksheet_Activateイベント内に記述する。このイベントを表示させる方法は前述しているので、そちらを参照してほしい。

Option Explicit
Private Sub Worksheet_Activate()
  Dim myCommandBar As CommandBar
  Dim myCommandBarControl As CommandBarControl
  Set myCommandBar = Application.CommandBars("Cell")
  myCommandBar.Reset
 
  Set myCommandBarControl = myCommandBar.Controls.Add(Before:=1, Type:=msoControlPopup)
  With myCommandBarControl
    .Caption = "文字"
    With .Controls.Add
      .Caption = "文字を表示"
      .OnAction = "文字を表示"
    End With
    
    With .Controls.Add
      .Caption = "文字の色"
      .OnAction = "文字の色"
    End With
  End With
End Sub

リスト3　複数のサブメニューを追加するコード

　まず、3行目でCommandBar型の変数「myCommandBar」を宣言している。CommandBarは、コンテナアプリケーションのコマンドバーを表す。次に、4行目でCommandBarControl型の変数「myCommandBarControl」を宣言している。CommandBarControlは、コマンドバーコントロールを表す。

　5行目でApplication.CommandBar("Cell")オブジェクトを、変数「myCommandBar」に格納し、6行目でResetメソッドで一度コマンドバー内をリセットする。この処理を行っていないと、メニューが重複して追加される。

　8行目では、表示位置をコマンドバーの先頭に指定し、TypeにmsoControlPopupを指定した、myCommandBarオブジェクトを、変数「myCommandBarControl」にセットしている。

　9〜10行目でmyCommandBarオブジェクトのCaptionプロパティに、「文字」と指定する。これが最初に表示される（親）メニューとなる。

　次に、11〜14行目でmyCommandBarのControls.Addメソッドを使い、Captionプロパティに「文字を表示」を指定し、onActionプロパティで実行するマクロとして「文字を表示」と指定する。同様に16〜19行目で、「文字」メニューを選択した場合に「文字を表示」と同列に表示されるメニュー「文字の色」を追加して、「文字の色」マクロを実行させる。この2つのメニューは、先ほどの「文字」メニューを親としたら、それにひも付く子のメニュー（サブメニュー）となる。

　次に、OnActionプロパティに指定したマクロであるリスト4とリスト5を書いていく。これらのマクロは、VBE（Visual Basic Editor）のメニューから、［挿入］→［標準モジュール］と選択して追加された、Module1内に記述する必要がある。リスト3と同じSheet1（Sheet1）内に記述しても動作しないので注意してほしい。

　まずは、リスト4の、「文字を表示」のコードからだ。

Option Explicit
Sub 文字を表示()
  With Range("B2")
    .Clear
    .Value = "Excel 2013 VBA"
    .Font.Name = "Meiryo UI"
    .Font.Size = 40
  End With
End Sub

リスト4　文字を表示するコード

　まず、4行目で文字を表示するセル「B2」をクリアしておく。書式も内容も全てクリアされる。次に、Valueプロパティに「Excel 2013 VBA」、書体に「Meiryo UI」、文字サイズに「40」を指定して、セル「B2」に表示する（5〜7行目）。

　次は、リスト5の「文字の色」のコードを記述する。

Sub 文字の色()
  If Range("B2").Value = "" Then
    MsgBox "「文字を表示」を先に選択してください。"
    Exit Sub
  Else
    Range("B2").Font.ColorIndex = 3
  End If
End Sub

リスト5　「文字の色」を指定するコード

　2〜4行目で、「文字を表示」サブメニューを選択せずに、先に「文字の色」サブメニューを選択したかどうかを判定し、選択した場合は、警告メッセージを表示して処理を抜ける。

　それ以外の場合は、6行目でセル「B2」に表示されている文字の色を「3（赤）」に指定している。

　実際に動くか確認してみよう。「Sheet1」をアクティブにして、マウスの右クリックでコマンドバーを表示させると、「文字」というメニューが先頭に追加され、マウスを持っていくとサブメニューが表示される（図4）。

図4　コマンドバーに「文字」メニューが表示され、サブメニューも表示された

　「文字を表示」を選択し（図5上）、「文字の色」を選択すると図5下のように表示される。

図5　「Excel 2013 VBA」の文字が指定した書体、サイズ、色で表示された

マクロの呼び出し方はいろいろあることを覚えておこう

　今回は、右クリックメニューにコマンドを登録してマクロと関連付ける方法と、さらにそのサブメニューにもマクロを登録する方法を紹介したが、いかがだっただろうか。

　「右クリックメニューにコマンドを登録してマクロと関連付ける」の使い道はとても多いのではないだろうか。都度セル上にボタンを配置して、ボタンをクリックしてマクロを実行させるより、メニューに登録しておいて、そこから実行させる方がスマートかもしれない。本Tips連載では、シート上にボタンを配置してマクロを呼び出す場合が多いが、時と場合によって、このTipsを思い出して使い分けてほしい。

　「複数のサブメニューを追加する」Tipsは、「右クリックメニューにコマンドを登録してマクロと関連付ける」Tipsを進化させたものだと考えていいだろう。スマートな方法だと思うので、ぜひ応用して使ってもらえればと思う。

著者プロフィール

薬師寺 国安（やくしじ くにやす）　／　薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev（Oct 2003-Sep 2012）。

Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。

Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。