Excel VBA ショートカットのサブメニューにマクロ実行ボタンを作成する

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim ショートカット As CommandBar
Dim メインコントロール As CommandBarPopup
Dim サブコントロール As CommandBarButton
Set ショートカット = Application.CommandBars.Add _
(Name:="ショートカットメニュ-", Position:=msoBarPopup)
Set メインコントロール = ショートカット.Controls.Add _
(Type:=msoControlPopup)
メインコントロール.Caption = "マクロ実行メニュー"
Set サブコントロール = メインコントロール.Controls.Add
With サブコントロール
.Caption = "サブボタン1"
.FaceId = 6862
.Style = msoButtonIconAndCaption
.OnAction = "メッセージ1"
.BeginGroup = True
End With
Set サブコントロール = メインコントロール.Controls.Add
With サブコントロール
.Caption = "サブボタン2"
.FaceId = 343
.Style = msoButtonIconAndCaption
.OnAction = "メッセージ2"
.BeginGroup = True
End With
With ショートカット
.ShowPopup
.Delete
End With
Cancel = True
End Sub

3行目【Dim メインコントロール As CommandBarPopup】
作成された「コマンドバー」を表すCommandBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトが格納されている変数「ショートカット」をControls【ｺﾝﾄﾛｰﾙｽﾞ】プロパティで参照してCommandBarControls【ｺﾏﾝﾄﾞﾊﾞｰｺﾝﾄﾛｰﾙｽﾞ】コレクションのAdd【ｱﾄﾞ】メソッドで追加したコントロールを格納する変数「メインコントロール」をオブジェクト型(CommandBarPopup)で宣言します。

4行目【Dim サブコントロール As CommandBarButton】
作成された「サブメニューを持つメニュー」を表すCommandBarPopup【ｺﾏﾝﾄﾞﾊﾞｰﾎﾟｯﾌﾟｱｯﾌﾟ】オブジェクトが格納されている変数「メインコントロール」をControls【ｺﾝﾄﾛｰﾙｽﾞ】プロパティで参照して、CommandBarControls【ｺﾏﾝﾄﾞﾊﾞｰｺﾝﾄﾛｰﾙｽﾞ】コレクションのAdd【ｱﾄﾞ】メソッドで追加したコントロールを格納する変数「サブコントロール」をオブジェクト型(CommandBarButton)で宣言します。

5行目～6行目【Set ショートカット = Application.CommandBars.Add _
(Name:=”ショートカットメニュ-“, Position:=msoBarPopup)】
Applicatio【ｱﾌﾟﾘｹｰｼｮﾝ】オブジェクトのCammandBars【ｺﾏﾝﾄﾞﾊﾞｰｽﾞ】コレクションのAdd【ｱﾄﾞ】メソッドを使用して「コマンドバー」を追加し、オブジェクト変数の「ショートカット」に代入します。

引数Position【ﾎﾟｼﾞｼｮﾝ】にショートカットメニューを表すmsoBarPopup【ｴﾑｴｽｵｰﾊﾞｰﾎﾟｯﾌﾟｱｯﾌﾟ】を設定します。

7行目～8行目【Set メインコントロール = ショートカット.Controls.Add _
(Type:=msoControlPopup)】
5～6行目で追加した「コマンドバー」(CommandBarオブジェクト)をControls【ｺﾝﾄﾛｰﾙｽﾞ】プロパティで参照して、CommandBarControls【ｺﾏﾝﾄﾞﾊﾞｰｺﾝﾄﾛｰﾙｽﾞ】コレクションのAdd【ｱﾄﾞ】メソッドを使用して「コマンドバー」に「サブメニューを持つメニュー」(CommandBarPopupオブジェクト)を追加してオブジェクト変数「メインコントロール」に代入します。

9行目【メインコントロール.Caption = “マクロ実行メニュー”】
「サブメニューを持つメニュー」を表すCommandPopup【ｺﾏﾝﾄﾞﾎﾟｯﾌﾟｱｯﾌﾟ】オブジェクトが格納されている変数「メインコントロール」にCaption【ｷｬﾌﾟｼｮﾝ】プロパティを使用してコントロールに文字列を設定します。

10行目【Set サブコントロール = メインコントロール.Controls.Add】
「サブメニューを持つメニュー」を表すCommandPopup【ｺﾏﾝﾄﾞﾎﾟｯﾌﾟｱｯﾌﾟ】オブジェクトが格納されている変数「メインコントロール」をControls【ｺﾝﾄﾛｰﾙｽﾞ】プロパティで参照して、CommandBarControls【ｺﾏﾝﾄﾞﾊﾞｰｺﾝｵﾛｰﾙｽﾞ】コレクションのAdd【ｱﾄﾞ】メソッドを使用して「サブメニュー」を追加し、オブジェクト変数「サブコントロール」に代入します。

引数Type【ﾀｲﾌﾟ】を省略しているので「サブメニューを持たないボタン」のCommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトが追加されます。

11行目【With サブコントロール】
CommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトが格納されている、オブジェクト変数「サブコントロール」をWith【ｳｨｽﾞ】ステートメントで指定します。

12行目【.Caption = “サブボタン1″】
With【ｳｨｽﾞ】ステートメントで指定したCommandBarButtonオブジェクトのCaption【ｷｬﾌﾟｼｮﾝ】プロパティを使用して
コントロールに文字列を設定します。

13行目【.FaceId = 6862】
With【ｳｨｽﾞ】ステートメントで指定したCommandBarButtonオブジェクトのFaceID【ﾌｪｲｽｱｲﾃﾞｨ】プロパティにオブジェクトに表示するアイコンのID（番号）を設定します。

設定値については「コマンドバーのボタンイメージ FaceIDを取得する」をご覧ください。

14行目【.Style = msoButtonIconAndCaption】
With【ｳｨｽﾞ】ステートメントで指定したCommandBarButton【ｺﾏﾝﾄﾞﾎﾞﾀﾝ】オブジェクトのStyle【ｽﾀｲﾙ】プロパティを使用してコントロールの表示方法を「テキストとアイコン」に指定します。

15行目【.OnAction = “メッセージ1″】
With【ｳｨｽﾞ】ステートメントで指定したCommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトのOnAction【ｵﾝｱｸｼｮﾝ】プロパティを使用してコントロールを押したときに実行するマクロを文字列で登録します。

16行目【.BeginGroup = True】
With【ｳｨｽﾞ】ステートメントで指定したCommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトのBeginGroup【ﾋﾞｷﾞﾝｸﾞﾙｰﾌﾟ】プロパティにTrueを設定して区切り線を表示します。

18行目【Set サブコントロール = メインコントロール.Controls.Add】
「サブメニューを持つメニュー」を表すCommandPopup【ｺﾏﾝﾄﾞﾎﾟｯﾌﾟｱｯﾌﾟ】オブジェクトが格納されている変数「メインコントロール」をControls【ｺﾝﾄﾛｰﾙｽﾞ】プロパティで参照して、CommandBarControls【ｺﾏﾝﾄﾞﾊﾞｰｺﾝｵﾛｰﾙｽﾞ】コレクションのAdd【ｱﾄﾞ】メソッドを使用して「サブメニュー」を追加し、オブジェクト変数「サブコントロール」に代入します。

オブジェクト変数「サブコントロール」は最初に格納した「サブボタン1」のCommandBarButtonオブジェクトが格納されていますが、このAdd【ｱﾄﾞ】メソッドで作成したCommandButtonオブジェクトに入れ替えます。

引数Type【ﾀｲﾌﾟ】を省略しているので「サブメニューを持たないボタン」のCommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトが追加されます。

19行目～25行目
内容は11行目～16行目の解説と同じで、2つ目の「サブメニューのボタン」の設定になります。

26行目【With ショートカット】
「ショートカットメニュー」を表すCommandBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトが格納されているオブジェクト変数「ショートカット」をWith【ｳｨｽﾞ】ステートメントで指定します。

27行目【.ShowPopup】
With【ｳｨｽﾞ】ステートメントで指定したCommandBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトのShowPopup【ｼｮｳﾎﾟｯﾌﾟｱｯﾌﾟ】メソッドを使用して「ショートメニュー」を表すCommandBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトを表示します。

28行目【.Delete】
With【ｳｨｽﾞ】ステートメントで指定したCommandBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトのDelete【ﾃﾞﾘｲﾄ】メソッドを使用して、表示したCommandBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトを削除します。

ここで、CommandBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトを削除しないと次に、BeforeRightClick【ﾋﾞﾌｫｱﾗｲﾄｸﾘｯｸ】イベント（セルをマウス右ボタンでクリックしたとき）が発生したとき、既にあるCommandBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトをAdd【ｱﾄﾞ】メソッドで作成してしまうのでエラーになります。

29行目【Cancel = True】
BeforeRightClick【ﾋﾞﾌｫｱﾗｲﾄｸﾘｯｸ】イベント（セルをマウス右ボタンでクリックしたとき）をCancel【ｷｬﾝｾﾙ】にTrueを設定することでイベント処理を中止します。

この処理がない場合は、「オリジナルショートカットメニュー」が表示したあとに「標準のショートカットメニュー」が表示されます。

Private Sub メッセージ1()
MsgBox "サブボタン1がクリックされました。"
End Sub

Private Sub メッセージ2()
MsgBox "サブボタン2がクリックされました。"
End Sub