VBA-加载项菜单

工具需求：打开文件时，加载项显示，关闭文件时加载项删除

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
deleteMenus
End Sub

Private Sub Workbook_Open()
createMenus
End Sub

Option Explicit

'create menus when this workbook opened
Public Sub createMenus()
    deleteMenus
    Dim cbMyTool As CommandBar
    Dim cbbMyButton As CommandBarButton

    'Make the toolbar
    Set cbMyTool = CommandBars.Add

    'Add a button to the toolbar.
    Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
    With cbbMyButton
        .Caption = "BUTTON.Caption"
        .Style = msoButtonIconAndCaption
        .OnAction = "onClickBtn"
        .FaceId = 296
        .TooltipText = "button.TooltipText"
    End With

'The toolbar gets a name and is put on the screen.
   With cbMyTool
     .Name = "NPA Tools"
     .Visible = True
  End With
  
BeforeExit:
    Set cbMyTool = Nothing
    Set cbbMyButton = Nothing

    Exit Sub
ErrorHandle:
    Debug.Print Err.Description & " CreateMenus"
    Resume BeforeExit
End Sub

'delete menus we created before this workbook close.
Public Sub deleteMenus()
    'Removes the toolbar "Shortcuts".
    'If it doesn't exist we get an error,
    'and that is why we use On Error Resume Next.
    On Error Resume Next
    CommandBars("NPA Tools").Delete
End Sub

Public Sub onClickBtn()
    MsgBox ("hello add in")
End Sub