Excel-VBA ショートカットメニュー追加

質問

Excel-VBA ショートカットメニュー追加
マウス右クリックでのショートカットメニュー「テストＡ、テストＢ、テストＣ」を最上段に追加(挿入)したい、
CommandBarControls.Add メソッド
構文:式.Add(Type, Id, Parameter, Before, Temporary)
これのBeforeを定義すれば良さそうに思えるのですが、成功しません!?
正しい記述事例を教えて下さい。

参考(現状):下記は最後(末尾)にメニューが追加されてしまう状態です。
Public Sub ShortCutMenuAdd()
Dim newMenu As CommandBarControls

Set newMenu = Application.CommandBars("Cell").Controls
newMenu.Add Type:=msoControlButton, Temporary:=True
newMenu(newMenu.Count).Caption = "テストＡ"
newMenu(newMenu.Count).OnAction = "mymsgA"
newMenu(newMenu.Count).BeginGroup = True

newMenu.Add Type:=msoControlButton, Temporary:=True
newMenu(newMenu.Count).Caption = "テストＢ"
newMenu(newMenu.Count).OnAction = "mymsgB"

newMenu.Add Type:=msoControlButton, Temporary:=True
newMenu(newMenu.Count).Caption = "テストＣ"
newMenu(newMenu.Count).OnAction = "mymsgC"

Set newMenu = Nothing
End Sub

回答No.1

Wendy02 · ベストアンサー

>最上段に追加(挿入)したい、
このようにすればよいと思います。

Sub RightClickMenuAdding()
　'右クリックメニュー登録
　With Application.CommandBars("CELL")
'二重登録を避けるために入れる
　　On Error Resume Next
　　.Controls("テストＡ").Delete
　　.Controls("テストＢ").Delete
　　.Controls("テストＣ").Delete
　　On Error GoTo 0
　　With .Controls.Add _
　　　(Type:=msoControlButton, Before:=1, Temporary:=True)
　　　.BeginGroup = False
　　　.Caption = "テストＣ"
　　　.OnAction = "mymsgC"
　　End With　　
　　With .Controls.Add _
　　　(Type:=msoControlButton, Before:=1, Temporary:=True)
　　　.BeginGroup = False
　　　.Caption = "テストＢ"
　　　.OnAction = "mymsgB"
　　End With
　　With .Controls.Add _
　　　(Type:=msoControlButton, Before:=1, Temporary:=True)
　　　.BeginGroup = False '←True, False どちらでも良い
　　　.Caption = "テストＡ"
　　　.OnAction = "mymsgA"
　　End With
　End With
End Sub

お礼

早々の回答有難うございました。
非力な私なのですがやっと皆様の回答内容を理解できました。両者ともBerryGood-感謝。
参考1:指定に[After]が無いため苦肉の策として次のダミーを追記して実践運用する事としました。
With .Controls.Add(Type:=msoControlButton, Before:=1, Temporary:=True)
.BeginGroup = True '←グループの初め(横線)
.Enabled = False '←不活性化する
End With
参考2:二重登録回避策として最良かどうか半信半疑ですが、
「.Controls("テストＡ").Delete」ではなく、
次の「.Reset」で実践運用しています。
Application.CommandBars("Cell").Reset '←標準に戻す
以上

回答No.2

imogasi

既出回答で良いのだと思いますが、下記でもやってみました。
加える位置はBeforeで指定するようですが、Afterがあるかどうか判りません（エラーになった）。
追加位置と、インデックス番号を使った。Add－Selectがうまく行かなかったので。
配列で繰り回せるが、類似コードを３回記述した。
Sub ShortCutMenuAdd1()
Application.CommandBars("Cell").Controls.Add Type:=msoControlButton, Temporary:=True, Before:=1
With Application.CommandBars("CELL").Controls(1)
.Caption = "テストＡ"
.OnAction = "mymsgA"
.BeginGroup = True
End With
Application.CommandBars("Cell").Controls.Add Type:=msoControlButton, Temporary:=True, Before:=2
With Application.CommandBars("CELL").Controls(2)
.Caption = "テストＢ"
.OnAction = "mymsgB"
.BeginGroup = True
End With
Application.CommandBars("Cell").Controls.Add Type:=msoControlButton, Temporary:=True, Before:=3
With Application.CommandBars("CELL").Controls(3)
.Caption = "テストC"
.OnAction = "mymsgC"
.BeginGroup = True
End With
'Set newMenu = Nothing
End Sub
ーー
参考
ついでにテスト中に余分なまたは位置を誤った追加が行われたときは、訂正には下記を使いました。
適当にコメント記号をはずして実行しました。
インデックス番号での指定（位置での指定）がわかりやすかった。
Public Sub ShortCutMenuAdd() 'Excel-VBA ショートカットメニューアイテム表示・削除
Dim newMenu As CommandBarControls
Dim ctl
' With Application.CommandBars("CELL")
For Each ctl In Application.CommandBars("CELL").Controls
MsgBox ctl.Index & " " & ctl.Caption
Next
With Application.CommandBars("CELL")
'.Controls(1).Delete
'.Controls(2).Delete
'.Controls(3).Delete
.Controls(4).Delete
'.Controls(5).Delete
'.Controls("テストＡ").Delete
'.Controls("テストＢ").Delete
'.Controls("テストＣ").Delete
End With
End Sub

お礼

早々の回答有難うございました。
複数の方から回答を頂いたのですが何れも実用的に申し分ない事例であると感謝しています。
両者は何れもベストアンサーなのですが、やむを得ず先に回答いただいた方をベストアンサーとした事をご理解下さい。
類似コードを３回記述するのも理解しやすくて良い方法だと思います。
次の記述はデバッグ時おおいに役にたちそう…使わせていただきます。
▽ショートカットメニューアイテム表示
For Each ctl In Application.CommandBars("CELL").Controls
MsgBox ctl.Index & " " & ctl.Caption
Next
以上