VBAでメニューの自作

要件

マクロの実行はシートにボタンを置くことは、シートが追加すると、再配置しないといけないです。
メニューに配置したいです。

案

・マクロは.xlsmファイルにモジュールとして置く
・ThisWorkSheetにメニューを作成する
　すでにある場合、先に削除する

実装

Public Sub AddMenu()

On Error Resume Next

  Set mns = Application.CommandBars("Worksheet Menu Bar")
  For Each mn In mns.Controls
   If mn.Caption = "TestJsonData(&T)" Then
     mn.Delete
   End If
  Next
  
    
    '
    Set NewM = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
    NewM.Caption = "TestJsonData(&T)"
    'Main menu
    Set NewC = NewM.Controls.Add
    With NewC
        .Caption = "Make Data2(&D)"
        .OnAction = "MakeData"
        .BeginGroup = False
        .FaceId = 277
    End With
    'menu 2
    Set NewC = NewM.Controls.Add
    With NewC
        .Caption = "Make Json2(&S)"
        .OnAction = "MakeJson"
        .BeginGroup = True
        .FaceId = 450
    End With
End Sub

Private Sub Workbook_Activate()
  AddMenu
End Sub

Private Sub Workbook_Close()
  Application.CommandBars("Worksheet Menu Bar").Controls("TestJsonData(&T)").Delete
End Sub

結果

ポイント①

メニューに該当「TestJsonData(&T)」があれば、削除する

  Set mns = Application.CommandBars("Worksheet Menu Bar")
  For Each mn In mns.Controls
   If mn.Caption = "TestJsonData(&T)" Then
     mn.Delete
   End If
  Next

ポイント②

該当ファイルを開くと、メニューを表示する、閉じると削除する

Private Sub Workbook_Activate()
  AddMenu
End Sub

Private Sub Workbook_Close()
  Application.CommandBars("Worksheet Menu Bar").Controls("TestJsonData(&T)").Delete
End Sub

ポイント③

Moduleと該当ソースの配置