連載
» 2014年08月25日 18時00分 UPDATE

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

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

[薬師寺国安,PROJECT KySS]
「VBA/マクロ便利Tips」のインデックス

連載目次

※本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の方法で自動的にコードが挿入される。

ExcelVBAOther3_03.png 図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)。

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

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

ExcelVBAOther4_05.jpg 図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)。

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

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

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

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

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

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

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

著者プロフィール

kuniyasu.jpg

薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所

薬師寺国安事務所代表。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)。


Copyright© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

この記事に関連するホワイトペーパー

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。