vba - VBA删除自定义添加选项卡

Question

我从 Stack Overflow 中找到了代码，它允许我通过将 xlam 文件作为加载项上传来添加自定义选项卡（参见下面的代码）。不幸的是，Deactivate当我从“开发人员”选项卡中取消选择加载项时，该部分不会从功能区中删除自定义选项卡。

我已经看到了可以通过 隐藏选项卡的示例GetVisible，但我不知道如何将其与下面的代码合并。而且我还没有找到任何更简单的操作来识别和删除从开发人员选项卡中取消选择加载项时的选项卡。

Private Sub Workbook_Open()

    Dim hFile As Long
    Dim path As String, fileName As String, ribbonXML As String, user As String

    hFile = FreeFile
    user = Environ("Username")
    path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
    fileName = "Excel.officeUI"

    ribbonXML = "<mso:customUI       xmlns:mso='http://schemas.microsoft.com/office/2009/07/customui'>" & vbNewLine
    ribbonXML = ribbonXML + "  <mso:ribbon>" & vbNewLine
    ribbonXML = ribbonXML + "    <mso:qat/>" & vbNewLine
    ribbonXML = ribbonXML + "    <mso:tabs>" & vbNewLine
    ribbonXML = ribbonXML + "      <mso:tab id='reportTab' label='Misc' insertBeforeQ='mso:TabFormat'>" & vbNewLine
    ribbonXML = ribbonXML + "        <mso:group id='reportGroup' label='Source Sheet' autoScale='true'>" & vbNewLine
    ribbonXML = ribbonXML + "          <mso:button id='runReport' label='Create Sheet' " & vbNewLine
    ribbonXML = ribbonXML + "imageMso='AppointmentColor3'      onAction='CreateSheet'/>" & vbNewLine
    ribbonXML = ribbonXML + "        </mso:group>" & vbNewLine
    ribbonXML = ribbonXML + "        <mso:group id='Group2' label='Formatting' autoScale='true'>" & vbNewLine
    ribbonXML = ribbonXML + "          <mso:button id='FormatButtons' label='Format Selection' " & vbNewLine
    ribbonXML = ribbonXML + "imageMso='AppointmentColor3'      onAction='Test2'/>" & vbNewLine
    ribbonXML = ribbonXML + "        </mso:group>" & vbNewLine
    ribbonXML = ribbonXML + "      </mso:tab>" & vbNewLine
    ribbonXML = ribbonXML + "    </mso:tabs>" & vbNewLine
    ribbonXML = ribbonXML + "  </mso:ribbon>" & vbNewLine
    ribbonXML = ribbonXML + "</mso:customUI>"

    ribbonXML = Replace(ribbonXML, """", "")

    Open path & fileName For Output Access Write As hFile
    Print #hFile, ribbonXML
    Close hFile

End Sub

Private Sub Workbook_Deactivate()

    Dim hFile As Long
    Dim path As String, fileName As String, ribbonXML As String, user As String

    hFile = FreeFile
    user = Environ("Username")
    path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
    fileName = "Excel.officeUI"

    ribbonXML = "<mso:customUI           xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">" & _
    "<mso:ribbon></mso:ribbon></mso:customUI>"

    Open path & fileName For Output Access Write As hFile
    Print #hFile, ribbonXML
    Close hFile

End Sub

标签： vbaribbonoffice-addins