Excel VBA サブメニューがあるボタン（マクロ実行ボタン）を作成する

Private Sub Workbook_Open()
Dim ボタン As CommandBar
Dim メインコントロール As CommandBarPopup
Dim サブコントロール As CommandBarButton
On Error Resume Next
CommandBars("オリジナルボタン").Delete
Set ボタン = Application.CommandBars.Add _
(Name:="オリジナルボタン", temporary:=True)
Set メインコントロール = ボタン.Controls.Add _
(Type:=msoControlPopup, temporary:=True)
With メインコントロール
.Caption = "オリジナルメニュー"
.TooltipText = "サブボタンを開きます。"
End With
Set サブコントロール = メインコントロール.Controls.Add _
(temporary:=True)
With サブコントロール
.Caption = "サブボタン1"
.FaceId = 6862
.Style = msoButtonIconAndCaption
.OnAction = "メッセージ1"
.BeginGroup = True
.TooltipText = "メッセージ1マクロを実行します。"
End With
Set サブコントロール = メインコントロール.Controls.Add _
(temporary:=True)
With サブコントロール
.Caption = "サブボタン2"
.FaceId = 343
.Style = msoButtonIconAndCaption
.OnAction = "メッセージ2"
.BeginGroup = True
.TooltipText = "メッセージ2マクロを実行します。"
End With
ボタン.Visible = True
End Sub

3行目【Dim メインコントロール As CommandBarPopup】
追加したコマンドバーに追加する「サブメニューを持つコントロール」を格納する変数「コントロール」をオブジェクト型(CommandBarPopup)で宣言します。

4行目【Dim サブコントロール As CommandBarButton】
「サブメニューを持つコントロール」のサブメニューのコントロールを格納する
変数「サブコントロール」をオブジェクト型(CommandBarButton)で宣言します。

5行目【On Error Resume Next】
次の行6行目で、CommandBarオブジェクトのDelete【ﾃﾞﾘｲﾄ】メソッドで「オリジナルボタン」という名前のCommandBarオブジェクトを削除する命令をだしますが、対象オブジェクトが存在しない場合、エラーが発生してデバックモードになってしまうので、On Error【ｵﾝｴﾗｰ】ステートメントのResume Next【ﾘｽﾞｰﾑﾈｸｽﾄ】ステートメントを使用してエラーを無視して処理を続行させます。

6行目 【CommandBars(“オリジナルボタン”).Delete】
次の7行目で、コマンドバーを追加しますが、既に同じ名前のコマンドバーが既に存在しているとエラーになるので、Application【ｱﾌﾟﾘｹｰｼｮﾝ】オブジェクトのCommansBars【ｺﾏﾝﾄﾞﾊﾞｰｽﾞ】プロパティで名前が「オリジナルボタン」のCommandBarオブジェクトを参照して、存在する場合は削除します。

7行目～8行目【Set ボタン = Application.CommandBars.Add _
(Name:=”オリジナルボタン”, Temporary:=True)】
Application【ｱﾌﾟﾘｹｰｼｮﾝ】オブジェクトのCommandBars【ｺﾏﾝﾄﾞﾊﾞｰｽﾞ】コレクションのAdd【ｱﾄﾞ】メソッドで引数Name【ﾈｰﾑ】に指定した名前のコマンドバーを追加してオブジェクト変数の「ボタン」に代入します。引数Temporary【ﾃﾝﾎﾟﾗﾘ】にTrueを設定したのでこのブックが閉じられたときにコマンドバーは削除されます。Falseに設定した場合はこのブックが閉じられてもコマンドバーは残り、他のブックを開いた場合にも追加したコマンドバーは表示されます。

9行目～10行目【Set メインコントロール = ボタン.Controls.Add _
(Type:=msoControlPopup, temporary:=True)】
CommandBarConrols【ｺﾏﾝﾄﾞﾊﾞｰｺﾝｵﾛｰﾙｽﾞ】コレクションのAdd【ｱﾄﾞ】メソッドでコマンドバーのコントロールを表すCommandBarControl【ｺﾏﾝﾄﾞﾊﾞｰｺﾝﾄﾛｰﾙ】オブジェクトのCommandBarPopup【ｺﾏﾝﾄﾞﾊﾞｰﾎﾟｯﾌﾟｱｯﾌﾟ】オブジェクトをオブジェクト変数の「メインコントロール」に代入します。CommandBarConrols【ｺﾏﾝﾄﾞﾊﾞｰｺﾝｵﾛｰﾙｽﾞ】コレクションは
CommandBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトのControls【ｺﾝﾄﾛｰﾙｽﾞ】プロパティを使用して参照します。「サブメニューを持つコントロール」を作成するので引数Type【ﾀｲﾌﾟ】には「msoControlPopup」を設定します。

11行目【With メインコントロール】
「サブメニューを持つコントロール」を表すCommandBarPopup【ｺﾏﾝﾄﾞﾊﾞｰﾎﾟｯﾌﾟｱｯﾌﾟ】オブジェクトが格納されているオブジェクト変数「メインコントロール」をWith【ｳｨｽﾞ】ステートメントで指定します。

12行目【.Caption = “オリジナルメニュー”】
CommandBarPopup【ｺﾏﾝﾄﾞﾊﾞｰﾎﾟｯﾌﾟｱｯﾌﾟ】オブジェクトのCaption【ｷｬﾌﾟｼｮﾝ】プロパティを使用してコントロールに表示する文字列を指定します。

13行目【.TooltipText = “サブボタンを開きます。”】
CommandBarPopup【ｺﾏﾝﾄﾞﾊﾞｰﾎﾟｯﾌﾟｱｯﾌﾟ】オブジェクトのTooltipText【ﾂｰﾙﾁｯﾌﾟﾃｷｽﾄ】プロパティを使用してマウスポインターがコントロール上にあるときに表示する「ポップヒント」の文字列を指定します。

15行目～16行目【Set サブコントロール = メインコントロール.Controls.Add _
(temporary:=True)】
CommandBarPopup【ｺﾏﾝﾄﾞﾊﾞｰﾎﾟｯﾌﾟｱｯﾌﾟ】オブジェクトが格納されている変数「メインコントロール」をContorols【ｺﾝﾛﾄｰﾙｽﾞ】プロパティで参照して、Add【ｱﾄﾞ】メソッドを使用してコントロールを追加し、オブジェクト変数の「サブコントロール」に代入します。Add【ｱﾄﾞ】メソッドの引数Type【ﾀｲﾌﾟ】を省略しているので作成されるコントロールは、「サブメニューを持たないボタン」のCommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトが作成されます。

17行目【With サブコントロール】
15～16行目で作成した「サブメニューのボタン」を表すCommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトが格納されているオブジェクト変数「サブコントロール」をWith【ｳｨｽﾞ】ステートメントで指定します。

18行目【.Caption = “サブボタン1″】
CommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトのCaption【ｷｬﾌﾟｼｮﾝ】プロパティにオブジェクトに表示する名前を設定します。

19行目【.FaceId = 6862】
CommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトのFaceID【ﾌｪｲｽｱｲﾃﾞｨ】プロパティにオブジェクトに表示するアイコンのID（番号）を設定します。設定値については「コマンドバーのボタンイメージ FaceIDを取得する」をご覧ください。

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

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

22行目【.BeginGroup = True】
CommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトのBeginGroup【ﾋﾞｷﾞﾝｸﾞﾙｰﾌﾟ】プロパティにTrueを設定して区切り線を表示します。

23行目【.TooltipText = “メッセージ1マクロを実行します。”】
CommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトのTooltipText【ﾂｰﾙﾁｯﾌﾟﾃｷｽﾄ】プロパティにポップヒントに表示する文字列を指定します。

25行目～34行目
「サブメニューを持つオブジェクト」CommandBarPopup【ｺﾏﾝﾄﾞﾊﾞｰﾎﾟｯﾌﾟｱｯﾌﾟ】オブジェクトに2つ目のコントロール（CommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクト）を追加して、プロパティで設定します。

CommandBarButton【ｺﾏﾝﾄﾞﾊﾞｰﾎﾞﾀﾝ】オブジェクトを格納するオブジェクト変数「サブコントロール」は15行目～23行目で作成した「サブボタン1」のCommandbarButtonオブジェクトを取り出して新たに作成する「サブボタン2」のCommandbarButtonオブジェクトを格納します。作成方法やプロパティの設定方法は「サブボタン1」と同じです。

35行目【ボタン.Visible = True】
CommandBar【ｺﾏﾝﾄﾞﾊﾞｰ】オブジェクトのVisible【ﾌﾞｼﾞﾌﾞﾙ】プロパティを使用して作成したコマンドバーを「アドイン」タブのリボンに表示します。

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

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