[Excel]VBAで「右クリックメニュー」を追加する

エクセル

前回の記事で紹介した「万年カレンダー」に実装した機能の作成方法を説明していきます。第一回は「右クリックメニュー」の追加です。まずはweb上で適当なサンプルを入手し、目的に合わせて改造するのが私のやり方です(一からコード書いてなんて無理)。事前準備としてマクロの実行ができるように[開発]タブを表示して下さい。表示方法がわからない方は以下の検索ボタンからどうぞ。

サンプルで試す

とりあえずググってサンプルを入手する

まずはググりましょう。

検索上位を読むとどうやら、 [CommandBars ]プロパティを使用して[ CommandBar]オブジェクトを参照する事でメニューを追加できるようです。うん、意味はよく分かりません。ただ参考になりそうなコードがあるのでコピペでいただきましょう。

VBAアイコンをクリック

1.[開発]タブで[Visual Basic]アイコンをクリック

標準モジュールをクリック

2.メニューの[挿入]から[標準モジュール]をクリック

サンプルをペースト

3.挿入された[Module1]をダブルクリックし、右側の[コードウィンドウ]へコピペする。

標準モジュールって何?と思うかも知れませんが、とりあえず無視しましょう(乱暴)。今の時点では「ここに書いたコードが実行される」位の理解でOKです。私がコピペしたコードは以下のページのものです。

右クリックメニューサンプル

Sub Sample1() 'マクロ名
    With CommandBars("Cell").Controls.Add(Type:=msoControlButton) '右クリックメニューを追加
        .Caption = "Button" '追加するメニューの名前
        .OnAction = "myMacro" 'メニューを押した時に実行されるマクロ名
    End With
End Sub

Sub myMacro() 'マクロ名
    MsgBox "Hello" 'メッセージボックスに「Hello」と表示される
End Sub

Sub Sample2() 'マクロ名
    CommandBars("Cell").Controls("Button").Delete '追加したメニューを削除する
End Sub

「’」の後に続く文字はマクロ実行時には無視されます。コメントとして利用するのが一般的なようです。改造の際、わかりやすいようにコメントを入れていますが、不要なら削除して下さい。

VBAの画面を閉じ(右上の×を押せば閉じます)、早速コピペしたコードを実行してみましょう。

VBAのコードはエクセルのファイルと一緒に保存されるのでここで保存しなくても消える事はありません。が、VBAを触っていると不測の事態が起きやすいのでまめに保存しておいた方がいいかも知れません。気になる方はVBA画面にも保存アイコンがあるのでそこを押して保存して下さい。

サンプルを実行して動きを確認する

マクロアイコンをクリック

1.[開発]タブの[マクロ]アイコンを押し、「sample1」を選択、実行する。

ボタンメニュー追加

2.右クリックすると、最下段に「Button」メニューが追加されている。

メッセージボックス表示

3.「Button」を押すと、メッセージボックスが表示される。

これで右クリックメニューが追加されました。しかしこの追加メニュー、やっかいな事に削除してやらないとずっと残ります(このファイルを閉じても他のファイルにも反映されます)。削除しないでSample1を実行するとさらに同じものが追加されます。現段階ではSample1を実行したら、 Sample2を実行して削除するようにしてください。もちろん、最終的には自動で削除するようにします。

サンプルコードを改造して自分のやりたい事を実装する

次に、実際にやりたい事を挙げてみます。以下は前回記事のカレンダーで実装した内容になります。

  • 追加したメニューを一番上に表示したい
  • メニューを2段階のリストにしたい
  • 右クリックメニューを表示するのは特定のシートのみにしたい

あとは検索ワードをひねり出して、ひたすらググるのみです。

メニューの表示位置とサブメニューの追加

以下のページにリストの1番目と2番目の答えがありました。

コードを参考にして先ほどのコードを改造します(といってもほぼ原文ママ)。

メニュー一番上+サブメニュー追加

Sub Sample1() 
    With CommandBars("Cell").Controls.Add(Before:=1, Type:=msoControlPopup) 'Before:= で表示位置(行)、Type:= でコントロールの種類を設定
        .Caption = "Button" '追加するメニューの名前
            With .Controls.Add 'サブメニューを追加
                 .Caption = "myMacro" 'サブメニュー(1段目)の名前
                 .OnAction = "myMacro" 'メニューを押した時に実行されるマクロ名
            End With
            With .Controls.Add
                 .Caption = "myMacro1" 'サブメニュー(2段目)の名前
                 .OnAction = "myMacro1" 'メニューを押した時に実行されるマクロ名
            End With
    End With
End Sub

Sub myMacro() 'マクロ名
    MsgBox "Hello" 'メッセージボックスに「Hello」と表示される
End Sub

Sub myMacro1() 'マクロ名
    MsgBox "ハロー" 'メッセージボックスに「ハロー」と表示される
End Sub

Sub Sample2() 'マクロ名
    CommandBars("Cell").Controls("Button").Delete '追加したメニューを削除する
End Sub
サブメニュー追加

Sample1 を実行すると1番上にサブメニューが追加されました。

Before:=1の数字の部分で表示位置(上から何行目か)を決めているようです(0にするとエラーになりました)。Type:=msoControlPopupで目的の2段階リスト(サブメニュー)にできました。With~End Withのくだりは私、最初は何が何だかさっぱりわかりませんでした。前回の記事で作成したファイルが完成する頃に何となく分かってきた感じでしたが、この段階では理解せずにそのまま使っていました。「動けばいい」の精神で潔く無視しましょう。

特定のシートでのみ、右クリックメニューを表示する

検索ワードをひねり出しました(ひねってない)。

検索上位を読むと、「ワークシートがアクティブになった(選択された)時」にマクロを実行する方法と「特定のワークシートで右クリックした時」にマクロを実行する方法があるようです。それぞれのメリットとデメリットがわからないのでエイヤーで決めました。「特定のワークシートで右クリックした時」に実行するようにします。

「Worksheetのイベントプロシージャ」を記述します。準備としてシートを一つ追加して下さい(sheet1とsheet2を用意する)。また、上の記事内の「Worksheet_BeforeRightClickイベント」1項から4項の手順を実施して[sheet1]の 「Worksheet_BeforeRightClick イベントプロシージャ」を作成して下さい。 準備ができたらsample1With ~ End Withまで切り取って、準備した「 Worksheet_BeforeRightClick プロシージャ」内に貼り付けます。 切り取った後、残ったSub Sample1() ‘マクロ名~End Subは削除して下さい。

イベントプロシージャの意味はここでは理解できてなくても問題ありません。私も実はあんまりわかってなかったりして…

[sheet1]でのみメニュー追加

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)'右クリック(の直前)でイベント開始
    With CommandBars("Cell").Controls.Add(Before:=1, Type:=msoControlPopup)  'sample1をここに貼り付け
        .Caption = "Button" 
            With .Controls.Add 
                 .Caption = "myMacro" 
                 .OnAction = "myMacro" 
            End With
            With .Controls.Add
                 .Caption = "myMacro1" 
                 .OnAction = "myMacro1"
            End With
    End With
End Sub
メニュー追加されすぎ

右クリックでイベントが発生し、無事メニューが追加される、のですが…

この状態で[sheet1]で右クリックをすると、実はメニューがどんどん増えていきます。「特定のシートで右クリック」をする度に発生する訳ですね。とりあえず増えたメニューをsample2実行で消します(マクロアイコンから実行して下さい)。全て消し終わったらsample2sample1同様に切り取って貼り付けます。貼り付け場所はWith ~の前です。メニューを追加する前に削除する事で差し引きゼロにする訳です。

無限増殖防止策

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    CommandBars("Cell").Controls("Button").Delete 'sample2をここに貼り付け
    With CommandBars("Cell").Controls.Add(Before:=1, Type:=msoControlPopup)
        .Caption = "Button"
            With .Controls.Add 
                 .Caption = "myMacro" 
                 .OnAction = "myMacro" 
            End With
            With .Controls.Add
                 .Caption = "myMacro1" 
                 .OnAction = "myMacro1"
            End With
    End With
End Sub
エラーダイアログ

右クリックすると、エラーが出ました

すると今度はエラーになります。どうも追加メニューがない状態でメニューを削除しようとするとエラーになるようです。先ほどの記事内でメニューを削除するコードとしてApplication.CommandBars(“Cell”).Resetが書かれていました。こいつを試してみましょう。CommandBars(“Cell”).Controls(“Button”).DeleteApplication.CommandBars(“Cell”).Resetを置き換えてみます。

エラーダイアログで[デバッグ]を選択するとVBA画面に戻り、エラー箇所が黄色でハイライトされます。[終了]を押すと実行中のマクロを終了し、エクセル画面に留まります。

無限増殖防止策2

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Application.CommandBars("Cell").Reset '追加したメニューをリセットする
    With CommandBars("Cell").Controls.Add(Before:=1, Type:=msoControlPopup) 
        .Caption = "Button" 
            With .Controls.Add 
                 .Caption = "myMacro"
                 .OnAction = "myMacro"
            End With
            With .Controls.Add
                 .Caption = "myMacro1"
                 .OnAction = "myMacro1"
            End With
    End With
End Sub
エラー回避成功

エラーになりませんでした。

これでいいじゃんと思ったものの、違いが気になるのでググりました。

自分で追加したメニューを削除するときにはDelete、全ての追加メニュー(他人が追加したものも含む)を初期化するときにはResetを使うようです。Resetでエラー吐かないならそれでいいかと思った矢先、以下のサイトにお叱りの言葉が書かれていました。

自分が追加したメニューやコマンドを削除するとき、メニューを初期化するためのResetメソッドは、できるだけ使わないようにしてください。Resetメソッドを実行すると、他のマクロやアドインが追加したメニューやコマンドもすべて削除されてしまうからです。メニューは、あなただけのものではありません。他のマクロやアドインなどがメニューを操作する可能性も十分に考慮して、メニューの追加や削除は慎重に行うようにしてください。

https://www.moug.net/tech/exvba/0080026.html

海より深く反省しました。という事で元々使用していた CommandBars(“Cell”).Controls(“Button”).Deleteでどうにかする事にします。Deleteが使えなかったのはそもそもエラーのせいです。エラーについて検索してみます。

エラーが発生した際、処理をスキップする方法がありました。On Error Resume Nextと書けば、ある処理がエラーになってもその次の処理へスキップしてくれるようです。

エラー処理追加

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next 'エラーが出たら次の処理へスキップ
    CommandBars("Cell").Controls("Button").Delete 'ここがエラーになったら
    With CommandBars("Cell").Controls.Add(Before:=1, Type:=msoControlPopup) 'ここまでスキップしてくれる
        .Caption = "Button" 
            With .Controls.Add
                 .Caption = "myMacro"
                 .OnAction = "myMacro"
            End With
            With .Controls.Add
                 .Caption = "myMacro1"
                 .OnAction = "myMacro1"
            End With
    End With
End Sub
エラー処理成功

無事動いたので、エラーはちゃんとスキップされたようです。が、[sheet2]でもメニューが表示されます。

無事動きました。が、[sheet2]でも追加メニューは表示されたままです。CommandBars(“Cell”).Controls(“Button”).Deleteが実行されるのは[sheet1]が右クリックされた時のみです。[sheet1]のWorksheetイベントに書いてあるので当たり前ですね。[sheet2]でメニューを無効にするためにどのイベントを利用すればいいか考えました。「Worksheet_BeforeRightClickイベントプロシージャ 」を作成する際にちらっと見えた「Worksheet_Deactivate」が使えそうです。Deactivateはおそらく「アクティブではなくなった時」って意味っぽいじゃないですか。早速プロシージャを作成してみましょう。

Worksheet_BeforeRightClickイベントプロシージャを作成した時と同じ方法です。イベントでDeactivateを選択して下さい。

他のシートでメニューが追加されない為の処理

Private Sub Worksheet_Deactivate() 'ワークシートがアクティブでなくなった時にイベント発生
    On Error Resume Next
    CommandBars("Cell").Controls("Button").Delete
End Sub
別シート処理成功

[sheet2]で右クリックをしても追加メニューは現れません。

シートについての処理はこれで完了です。あと一息で完成です。

ブックの処理

色々テストを重ねた結果、

  • 他のファイルがアクティブになった時
  • ファイルを閉じた時

にメニュー削除の処理をしないとメニューが残ったままになります。シート同様、ファイルについての処理が必要という事ですね。ググりましょう。

エクセルでしか操作できないファイルの事をブックと呼ぶそうです( *.xlsx , *.xlsm等の形式で保存されたファイル)。今後、頻繁に出てくる用語なので覚えておきましょう。乱暴ですが、今回の記事内ではファイル=ブックと捉えて問題ありません。

Workbook_BeforeCloseAuto_Closeの2通りの方法があるようです。それぞれ微妙に違うようですが、アドバイスがありました。

Auto_Closeは以前のバージョンとの互換性を保持するために残されているものですので、

特段の理由がなければ、Workbook_BeforeCloseのみを使用した方が良いでしょう。

はい、特段の理由はないのでWorkbook_BeforeCloseを使用します。 「Microsoft Excel Objects」内の「ThisWorkbook」に記述しろと書いてあるので、

workbookオブジェクト

VBA画面のプロジェクトウィンドウ内のThisWorkbookをダブルクックします。

Workbookオブジェクト2

オブジェクトでWorkbookを選択します。この時勝手にイベントが選択され、プロシージャが生成されますが無視して下さい。

Workbookイベント

右側のプロシージャからBeforeCloseを選択します。先ほど勝手に生成されたプロシージャは削除します。

Workbook_BeforeCloseイベントプロシージャを作成する際、ちらっとDeactivateの文字が見えたので「他のファイルがアクティブになったとき」の処理もまとめて書きました。

ブックの処理

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'ブックが閉じる時にイベントが発生
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Button").Delete
End Sub

Private Sub Workbook_Deactivate() 'ブックがアクティブでなくなった時にイベント発生
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Button").Delete
End Sub

いきなりApplication.CommandBars(“Cell”).Controls(“Button”).Deleteになっていますね。 Application. を追加しています。以下、理由です。

標準モジュールではアクティブブックがデフォルトのブックになり、ThisWorkbookのモジュールではそのブックがデフォルトのブックになります。 ThisWorkbookモジュールでは…

CommandBars(“Cell”).ShowPopup…×誤り。ThisWorkbook.CommandBars の意味になる

Application.CommandBars(“Cell”).ShowPopup…○正しい。セルのショートカットメニューを表示する

https://vbae.odyssey-com.co.jp/column4/s41502.html

何と、コードを書く場所によって参照するオブジェクトが変わるようです。要は今まで省略しててもたまたま問題なかったって事ですね。これは難しいので理解は後回しです。ググって拾ってきたサンプルを適当に使いまわすとこういう目に合います。が、助けてくれるのも「ググる」なのです。

これで無事完成です。適当に複数のファイルを開いた状態で、右クリックをしてみて下さい。狙った通りの動きになっているはずです。

完成

以下、今回の出来高です。

Worksheetイベントプロシージャ

'右クリックでメニュー追加
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    CommandBars("Cell").Controls("Button").Delete
    With CommandBars("Cell").Controls.Add(Before:=1, Type:=msoControlPopup)
        .Caption = "Button" 'メニュー名
            With .Controls.Add
                 .Caption = "myMacro" 'サブメニュー名
                 .OnAction = "myMacro" '実行するマクロ名
            End With
            With .Controls.Add
                 .Caption = "myMacro1" 'サブメニュー名
                 .OnAction = "myMacro1" '実行するマクロ名
            End With
    End With
End Sub

'シートがアクティブでなくなった時、メニューを削除する
Private Sub Worksheet_Deactivate()
    On Error Resume Next
    CommandBars("Cell").Controls("Button").Delete
End Sub
Workbookイベントプロシージャ

'ブックが閉じた時、メニューを削除する
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Button").Delete
End Sub

'ブックがアクティブでなくなった時、メニューを削除する
Private Sub Workbook_Deactivate()
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Button").Delete
End Sub
Subプロシージャ(標準モジュール内)

Sub myMacro() 
    MsgBox "Hello" 'メッセージボックスに「Hello」と表示される
End Sub

Sub myMacro1() 
    MsgBox "ハロー" 'メッセージボックスに「ハロー」と表示される
End Sub

ブックやシートはイベントが用意されているので、それをトリガーとしてマクロが実行できました。標準モジュール内に書いたsubプロシージャはブックやシートのイベントプロシージャに含める事によって実行できました。何となくマクロの動かし方が見えてきましたね。

これで右クリックメニューの完成です。機能としてはまだメッセージボックスが表示されるだけですが、右クリックメニューのひな型として今後も使えそうです。サブメニューを増やすのもコピペで簡単にできそうです。次回はこのメニューを利用して、選択したセルの書式を変更する機能を実装します。

最後までご高覧いただきありがとうございました。

タイトルとURLをコピーしました