当前位置：theitroad>如何使用 VBA 添加自定义功能区选项卡？

如何使用 VBA 添加自定义功能区选项卡？

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/8850836/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-08 12:24:47 　来源:igfitidea点击:

How to add a custom Ribbon tab using VBA?

excelvbaexcel-2007ribbonx

提问by BuZz

I am looking for a way to add a custom tab in the Excel ribbon which would carry a few buttons. I chanced on some resources addressing it via Google but all look dodgy and outrageously complicated.

我正在寻找一种在 Excel 功能区中添加自定义选项卡的方法，该选项卡带有几个按钮。我偶然通过谷歌找到了一些资源来解决这个问题，但看起来都很狡猾而且非常复杂。

What is a quick and simple way to do that ? I'd like the new tab to get loaded when my VBA gets loaded into Excel..

有什么快速而简单的方法可以做到这一点？我希望在我的 VBA 加载到 Excel 时加载新选项卡。

UPDATE: I tried this example from herebut get an "object required" error on the last instruction :

更新：我从这里尝试了这个例子，但在最后一条指令上得到一个“需要对象”的错误：

Public Sub AddHighlightRibbon()
Dim ribbonXml As String

ribbonXml = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"
ribbonXml = ribbonXml + "  <mso:ribbon>"
ribbonXml = ribbonXml + "    <mso:qat/>"
ribbonXml = ribbonXml + "    <mso:tabs>"
ribbonXml = ribbonXml + "      <mso:tab id=""highlightTab"" label=""Highlight"" insertBeforeQ=""mso:TabFormat"">"
ribbonXml = ribbonXml + "        <mso:group id=""testGroup"" label=""Test"" autoScale=""true"">"
ribbonXml = ribbonXml + "          <mso:button id=""highlightManualTasks"" label=""Toggle Manual Task Color"" "
ribbonXml = ribbonXml + "imageMso=""DiagramTargetInsertClassic"" onAction=""ToggleManualTasksColor""/>"
ribbonXml = ribbonXml + "        </mso:group>"
ribbonXml = ribbonXml + "      </mso:tab>"
ribbonXml = ribbonXml + "    </mso:tabs>"
ribbonXml = ribbonXml + "  </mso:ribbon>"
ribbonXml = ribbonXml + "</mso:customUI>"

ActiveProject.SetCustomUI (ribbonXml)
End Sub

回答by Siddharth Rout

AFAIK you cannot use VBA Excel to create custom tab in the Excel ribbon. You can however hide/make visible a ribbon component using VBA. Additionally, the link that you mentioned above is for MS Project and not MS Excel.

AFAIK 您不能使用 VBA Excel 在 Excel 功能区中创建自定义选项卡。但是，您可以使用 VBA 隐藏/显示功能区组件。此外，您上面提到的链接是针对 MS Project 而不是 MS Excel。

I create tabs for my Excel Applications/Add-Ins using this free utility called Custom UI Editor.

我使用这个名为Custom UI Editor 的免费实用程序为我的 Excel 应用程序/加载项创建选项卡。

Edit: To accommodate new request by OP

编辑：为了满足 OP 的新要求

Tutorial

教程

Here is a short tutorial as promised:

这是承诺的简短教程：

  1. After you have installed the Custom UI Editor (CUIE), open it and then click on File | Open and select the relevant Excel File. Please ensure that the Excel File is closed before you open it via CUIE. I am using a brand new worksheet as an example.

    enter image description here

  2. Right click as shown in the image below and click on "Office 2007 Custom UI Part". It will insert the "customUI.xml"

    enter image description here

  3. Next Click on menu Insert | Sample XML | Custom Tab. You will notice that the basic code is automatically generated. Now you are all set to edit it as per your requirements.

    enter image description here

  4. Let's inspect the code

    enter image description here

    label="Custom Tab": Replace "Custom Tab" with the name which you want to give your tab. For the time being let's call it "Jerome".

    The below part adds a custom button. 

    <button id="customButton" label="Custom Button" imageMso="HappyFace" size="large" onAction="Callback" />

    imageMso: This is the image that will display on the button. "HappyFace" is what you will see at the moment. You can download more image ID's here.

    onAction="Callback": "Callback" is the name of the procedure which runs when you click on the button.

  1. 安装自定义 UI 编辑器 (CUIE) 后，打开它，然后单击文件 | 打开并选择相关的 Excel 文件。在通过 CUIE 打开 Excel 文件之前，请确保它已关闭。我以一个全新的工作表为例。

    在此处输入图片说明

  2. 如下图所示右键单击，然后单击“Office 2007 自定义 UI 部件”。它将插入“customUI.xml”

    在此处输入图片说明

  3. 下一步 点击菜单插入 | 示例 XML | 自定义选项卡。您会注意到基本代码是自动生成的。现在您已准备好根据您的要求对其进行编辑。

    在此处输入图片说明

  4. 让我们检查代码

    在此处输入图片说明

    label="Custom Tab"：将“自定义选项卡”替换为您要为选项卡指定的名称。暂时让我们称它为“杰罗姆”。

    下面的部分添加了一个自定义按钮。 

    <button id="customButton" label="Custom Button" imageMso="HappyFace" size="large" onAction="Callback" />

    imageMso：这是将显示在按钮上的图像。“HappyFace”就是你此刻会看到的。您可以在此处下载更多图像 ID。

    onAction="Callback"：“回调”是单击按钮时运行的程序的名称。

Demo

演示

With that, let's create 2 buttons and call them "JG Button 1" and "JG Button 2". Let's keep happy face as the image of the first one and let's keep the "Sun" for the second. The amended code now looks like this:

有了这个，让我们创建 2 个按钮并将它们命名为“JG Button 1”和“JG Button 2”。让我们保持幸福的脸作为第一个形象，让我们保持“太阳”作为第二个形象。修改后的代码现在看起来像这样：

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab id="MyCustomTab" label="Jerome" insertAfterMso="TabView">
<group id="customGroup1" label="First Tab">
<button id="customButton1" label="JG Button 1" imageMso="HappyFace" size="large" onAction="Callback1" />
<button id="customButton2" label="JG Button 2" imageMso="PictureBrightnessGallery" size="large" onAction="Callback2" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

Delete all the code which was generated in CUIE and then paste the above code in lieu of that. Save and close CUIE. Now when you open the Excel File it will look like this:

删除所有在 CUIE 中生成的代码，然后粘贴上面的代码来代替。保存并关闭 CUIE。现在，当您打开 Excel 文件时，它将如下所示：

enter image description here

在此处输入图片说明

Now the code part. Open VBA Editor, insert a module, and paste this code:

现在是代码部分。打开 VBA 编辑器，插入一个模块，然后粘贴以下代码：

Public Sub Callback1(control As IRibbonControl)

    MsgBox "You pressed Happy Face"

End Sub

Public Sub Callback2(control As IRibbonControl)

    MsgBox "You pressed the Sun"

End Sub

Save the Excel file as a macro enabled file. Now when you click on the Smiley or the Sun you will see the relevant message box:

将 Excel 文件另存为启用宏的文件。现在，当您单击 Smiley 或 Sun 时，您将看到相关的消息框：

enter image description here

在此处输入图片说明

Hope this helps!

希望这可以帮助！

回答by Roi-Kyi Bryant

I was able to accomplish this with VBA in Excel 2013. No special editors needed. All you need is the Visual Basic code editor which can be accessed on the Developer tab. The Developer tab is not visible by default so it needs to be enabled in File>Options>Customize Ribbon. On the Developer tab, click the Visual Basic button. The code editor will launch. Right click in the Project Explorer pane on the left. Click the insert menu and choose module. Add both subs below to the new module.

我能够在 Excel 2013 中使用 VBA 完成此操作。不需要特殊的编辑器。您所需要的只是可以在“开发人员”选项卡上访问的 Visual Basic 代码编辑器。默认情况下，“开发工具”选项卡不可见，因此需要在“文件”>“选项”>“自定义功能区”中启用它。在开发工具选项卡上，单击 Visual Basic 按钮。代码编辑器将启动。右键单击左侧的 Project Explorer 窗格。单击插入菜单并选择模块。将下面的两个子项添加到新模块中。

Sub LoadCustRibbon()

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='Reports' insertBeforeQ='mso:TabFormat'>" & vbNewLine
ribbonXML = ribbonXML + "        <mso:group id='reportGroup' label='Reports' autoScale='true'>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='runReport' label='PTO' "   & vbNewLine
ribbonXML = ribbonXML + "imageMso='AppointmentColor3'      onAction='GenReport'/>" & 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

Sub ClearCustRibbon()

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

Call LoadCustRibbon sub in the Wookbook open even and call the ClearCustRibbon sub in the Before_Close Event of the ThisWorkbook code file.

在 Wookbook open even 中调用 LoadCustRibbon sub 并在 ThisWorkbook 代码文件的 Before_Close Event 中调用 ClearCustRibbon sub。

回答by Jan Wijninckx

I struggled like mad, but this is actually the right answer. For what it is worth, what I missed was is this:

我拼命挣扎，但这实际上是正确的答案。对于它的价值，我错过的是：

  1. As others say, one can't create the CustomUI ribbon with VBA, however, you don't need to!
  2. The idea is you create your xml Ribbon code using Excel's File > Options > Customize Ribbon, and then export the Ribbon to a .customUI file (it's just a txt file, with xml in it)
  3. Now comes the trick: you can includethe .customUI code in your .xlsm file using the MS tool they refer to here, by copying the code from the .customUI file
  4. Once it is included in the .xlsm file, every time you open it, the ribbon you defined is addedto the user's ribbon - but do use < ribbon startFromScratch="false" > or you lose the rest of the ribbon. On exit-ing the workbook, the ribbon is removed.
  5. From here on it is simple, create your ribbon, copy the xml code that is specific to your ribbon from the .customUI file, and place it in a wrapper as shown above (...< tabs> your xml < /tabs...)
  1. 正如其他人所说，无法使用 VBA 创建 CustomUI 功能区，但是，您不需要！
  2. 这个想法是您使用 Excel 的文件 > 选项 > 自定义功能区创建 xml 功能区代码，然后将功能区导出到 .customUI 文件（它只是一个 txt 文件，其中包含 xml）
  3. 现在技巧来了：您可以使用他们在此处引用的 MS 工具将 .customUI 代码包含在您的 .xlsm 文件中，方法是从 .customUI 文件中复制代码
  4. 一旦它包含在 .xlsm 文件中，每次打开它时，您定义的功能区都会 添加到用户的功能区 - 但一定要使用 <ribbon startFromScratch="false" > 否则你会丢失剩余的功能区。退出工作簿时，功能区将被移除。
  5. 从这里开始很简单，创建您的功能区，从 .customUI 文件复制特定于您的功能区的 xml 代码，并将其放置在如上所示的包装器中（...<tabs> 您的 xml </tabs.. .)

By the way the page that explains it on Ron's site is now at http://www.rondebruin.nl/win/s2/win002.htm

顺便说一下，罗恩网站上解释它的页面现在位于 http://www.rondebruin.nl/win/s2/win002.htm

And here is his example on how you enable /disable buttons on the Ribbon http://www.rondebruin.nl/win/s2/win013.htm

这是他关于如何在功能区上启用/禁用按钮的示例 http://www.rondebruin.nl/win/s2/win013.htm

For other xml examples of ribbons please also see http://msdn.microsoft.com/en-us/library/office/aa338202%28v=office.12%29.aspx

有关色带的其他 xml 示例，请参阅 http://msdn.microsoft.com/en-us/library/office/aa338202%28v=office.12%29.aspx

回答by Jomtung

The answers on here are specific to using the custom UI Editor. I spent some time creating the interface without that wonderful program, so I am documenting the solution here to help anyone else decide if they need that custom UI editor or not.

此处的答案特定于使用自定义 UI 编辑器。我花了一些时间在没有那个很棒的程序的情况下创建界面，所以我在这里记录解决方案以帮助其他人决定他们是否需要该自定义 UI 编辑器。

I came across the following microsoft help webpage - https://msdn.microsoft.com/en-us/library/office/ff861787.aspx. This shows how to set up the interface manually, but I had some trouble when pointing to my custom add-in code.

我遇到了以下微软帮助网页 - https://msdn.microsoft.com/en-us/library/office/ff861787.aspx。这显示了如何手动设置界面，但在指向我的自定义加载项代码时遇到了一些麻烦。

To get the buttons to work with your custom macros, setup the macro in your .xlam subs to be called as described in this SO answer - Calling an excel macro from the ribbon. Basically, you'll need to add that "control As IRibbonControl" paramter to any module pointed from your ribbon xml. Also, your ribbon xml should have the onAction="myaddin!mymodule.mysub" syntax to properly call any modules loaded by the add in.

要使按钮与您的自定义宏一起使用，请在您的 .xlam 子文件中设置要调用的宏，如此 SO 答案中所述 -从功能区调用 excel 宏。基本上，您需要将“control As IRibbonControl”参数添加到从您的功能区 xml 指向的任何模块。此外，您的功能区 xml 应该具有 onAction="myaddin!mymodule.mysub" 语法以正确调用加载项加载的任何模块。

Using those instructions I was able to create an excel add in (.xlam file) that has a custom tab loaded when my VBA gets loaded into Excel along with the add in. The buttons execute code from the add in and the custom tab uninstalls when I remove the add in.

使用这些说明，我能够创建一个 excel 加载项（.xlam 文件），当我的 VBA 与加载项一起加载到 Excel 中时，该加载项加载了一个自定义选项卡。按钮执行加载项中的代码，自定义选项卡卸载时我删除了插件。

回答by Erikas

In addition to Roi-Kyi Bryant answer, this code fully works in Excel 2010. Press ALT + F11 and VBA editor will pop up. Double click on ThisWorkbookon the left side, then paste this code:

除了 Roi-Kyi Bryant 的回答外，这段代码在 Excel 2010 中完全有效。按 ALT + F11 会弹出 VBA 编辑器。双击ThisWorkbook左侧，然后粘贴此代码：

Private Sub Workbook_Activate()

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='My Actions' insertBeforeQ='mso:TabFormat'>" & vbNewLine
ribbonXML = ribbonXML + "        <mso:group id='reportGroup' label='Reports' autoScale='true'>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='runReport' label='Trim' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='AppointmentColor3'      onAction='TrimSelection'/>" & 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

Don't forget to save and re-open workbook. Hope this helps!

不要忘记保存并重新打开工作簿。希望这可以帮助！

回答by Joshua Daly

I encountered difficulties with Roi-Kyi Bryant's solution when multipleadd-ins tried to modify the ribbon. I also don't have admin access on my work-computer, which ruled out installing the Custom UI Editor. So, if you're in the same boat as me, here's an alternative exampleto customising the ribbon using only Excel. Note, my solution is derived from the Microsoft guide.

当多个加载项尝试修改功能区时，我在使用 Roi-Kyi Bryant 的解决方案时遇到了困难。我的工作计算机也没有管理员访问权限，这排除了安装Custom UI Editor. 因此，如果您和我在同一条船上，这里有一个仅使用 Excel 自定义功能区的替代示例。请注意，我的解决方案源自Microsoft 指南。

  1. Create Excel file/files whose ribbons you want to customise. In my case, I've created two .xlamfiles, Chart Tools.xlamand Priveleged UDFs.xlam, to demonstrate how multiple add-ins can interact with the Ribbon.
  2. Create a folder, with any folder name, for eachfile you just created.
  3. Inside each of the folders you've created, add a customUIand _relsfolder.
  4. Inside each customUIfolder, create a customUI.xmlfile. The customUI.xmlfile details how Excel files interact with the ribbon. Part 2 of the Microsoft guidecovers the elements in the customUI.xmlfile.
  1. 创建要自定义其功能区的 Excel 文件。就我而言，我创建了两个.xlam文件Chart Tools.xlam和Priveleged UDFs.xlam，以演示多个加载项如何与功能区交互。
  2. 为您刚刚创建的每个文件创建一个具有任意文件夹名称的文件夹。
  3. 在您创建的每个文件夹中，添加一个customUI和_rels文件夹。
  4. 在每个customUI文件夹中，创建一个customUI.xml文件。该customUI.xml文件详细说明 Excel 文件如何与功能区交互。Microsoft 指南的第 2 部分涵盖了customUI.xml文件中的元素。

My customUI.xmlfile for Chart Tools.xlamlooks like this

我的customUI.xml文件Chart Tools.xlam看起来像这样

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="sao">
  <ribbon>
    <tabs>
      <tab idQ="x:chartToolsTab" label="Chart Tools">
        <group id="relativeChartMovementGroup" label="Relative Chart Movement" >
            <button id="moveChartWithRelativeLinksButton" label="Copy and Move" imageMso="ResultsPaneStartFindAndReplace" onAction="MoveChartWithRelativeLinksCallBack" visible="true" size="normal"/>
            <button id="moveChartToManySheetsWithRelativeLinksButton" label="Copy and Distribute" imageMso="OutlineDemoteToBodyText" onAction="MoveChartToManySheetsWithRelativeLinksCallBack" visible="true" size="normal"/>
        </group >
        <group id="chartDeletionGroup" label="Chart Deletion">
            <button id="deleteAllChartsInWorkbookSharingAnAddressButton" label="Delete Charts" imageMso="CancelRequest" onAction="DeleteAllChartsInWorkbookSharingAnAddressCallBack" visible="true" size="normal"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

My customUI.xmlfile for Priveleged UDFs.xlamlooks like this

我的customUI.xml文件Priveleged UDFs.xlam看起来像这样

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="sao">
  <ribbon>
    <tabs>
      <tab idQ="x:privelgedUDFsTab" label="Privelged UDFs">
        <group id="privelgedUDFsGroup" label="Toggle" >
            <button id="initialisePrivelegedUDFsButton" label="Activate" imageMso="TagMarkComplete" onAction="InitialisePrivelegedUDFsCallBack" visible="true" size="normal"/>
            <button id="deInitialisePrivelegedUDFsButton" label="De-Activate" imageMso="CancelRequest" onAction="DeInitialisePrivelegedUDFsCallBack" visible="true" size="normal"/>
        </group >
      </tab>
    </tabs>
  </ribbon>
</customUI>
  1. For eachfile you created in Step 1, suffix a .zipto their file name. In my case, I renamed Chart Tools.xlamto Chart Tools.xlam.zip, and Privelged UDFs.xlamto Priveleged UDFs.xlam.zip.
  2. Open each .zipfile, and navigate to the _relsfolder. Copy the .relsfile to the _relsfolder you created in Step 3. Edit each.relsfile with a text editor. From the Microsoft guide
  1. 对于您在步骤 1 中创建的每个文件，.zip为其文件名添加后缀 a 。就我而言，我改名Chart Tools.xlam来Chart Tools.xlam.zip，并Privelged UDFs.xlam到Priveleged UDFs.xlam.zip。
  2. 打开每个.zip文件，然后导航到_rels文件夹。将.rels文件复制到_rels您在步骤 3 中创建的文件夹。使用文本编辑器编辑每个.rels文件。来自微软指南

Between the final <Relationship>element and the closing <Relationships>element, add a line that creates a relationship between the document file and the customization file. Ensure that you specify the folder and file names correctly.

在最后一个<Relationship>元素和结束 <Relationships>元素之间，添加一行以创建文档文件和自定义文件之间的关系。确保正确指定文件夹和文件名。

<Relationship Type="http://schemas.microsoft.com/office/2006/
  relationships/ui/extensibility" Target="/customUI/customUI.xml" 
  Id="customUIRelID" />

My .relsfile for Chart Tools.xlamlooks like this

我的.rels文件Chart Tools.xlam看起来像这样

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
        <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
        <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
        <Relationship Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="/customUI/customUI.xml" Id="chartToolsCustomUIRel" />
    </Relationships>

My .relsfile for Priveleged UDFslooks like this.

我的.rels文件Priveleged UDFs看起来像这样。

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
        <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
        <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
        <Relationship Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="/customUI/customUI.xml" Id="privelegedUDFsCustomUIRel" />
    </Relationships>
  1. Replace the .relsfiles in each .zipfile with the .relsfile/files you modified in the previous step.
  2. Copy and paste the .customUIfolder you created into the home directory of the .zipfile/files.
  3. Remove the .zipfile extension from the Excel files you created.
  4. If you've created .xlamfiles, back in Excel, add them to your Excel add-ins.
  5. If applicable, create callbacksin each of your add-ins. In Step 4, there are onActionkeywords in my buttons. The onActionkeyword indicates that, when the containing element is triggered, the Excel application will trigger the sub-routine encased in quotation marks directly after the onActionkeyword. This is known as a callback. In my .xlamfiles, I have a module called CallBackswhere I've included my callback sub-routines.
  1. 将.rels每个.zip文件中的.rels文件替换为您在上一步中修改的文件。
  2. 将.customUI您创建的文件夹复制并粘贴到.zip文件/文件的主目录中。
  3. .zip从您创建的 Excel 文件中删除文件扩展名。
  4. 如果您已创建.xlam文件，请返回 Excel，将它们添加到您的 Excel 加载项中。
  5. 如果适用，请在每个加载项中创建回调。在第 4 步中，onAction我的按钮中有关键字。该onAction关键字指示，含元素被触发时，Excel应用程序将触发子例程后直接封装在引号onAction关键字。这称为回调。在我的.xlam文件中，我有一个名为的模块CallBacks，其中包含了我的回调子例程。

CallBacks Module

回调模块

My CallBacksmodule for Chart Tools.xlamlooks like

我的CallBacks模块Chart Tools.xlam看起来像

Option Explicit

Public Sub MoveChartWithRelativeLinksCallBack(ByRef control As IRibbonControl)
  MoveChartWithRelativeLinks
End Sub

Public Sub MoveChartToManySheetsWithRelativeLinksCallBack(ByRef control As IRibbonControl)
  MoveChartToManySheetsWithRelativeLinks
End Sub

Public Sub DeleteAllChartsInWorkbookSharingAnAddressCallBack(ByRef control As IRibbonControl)
  DeleteAllChartsInWorkbookSharingAnAddress
End Sub

My CallBacksmodule for Priveleged UDFs.xlamlooks like

我的CallBacks模块Priveleged UDFs.xlam看起来像

Option Explicit

选项显式

Public Sub InitialisePrivelegedUDFsCallBack(ByRef control As IRibbonControl)
  ThisWorkbook.InitialisePrivelegedUDFs
End Sub

Public Sub DeInitialisePrivelegedUDFsCallBack(ByRef control As IRibbonControl)
  ThisWorkbook.DeInitialisePrivelegedUDFs
End Sub

Different elements have a different callback sub-routine signature. For buttons, the required sub-routine parameter is ByRef control As IRibbonControl. If you don't conform to the required callback signature, you will receive an error while compiling your VBA project/projects. Part 3 of the Microsoft guidedefines all the callback signatures.

不同的元素有不同的回调子程序签名。对于按钮，所需的子程序参数是ByRef control As IRibbonControl。如果您不符合所需的回调签名，您将在编译 VBA 项目/项目时收到错误消息。Microsoft 指南的第 3 部分定义了所有回调签名。

Here's what my finished example looks like

这是我完成的示例的样子

Finished Product

完成的产品

Some closing tips

一些结束技巧

  1. If you want add-ins to share Ribbon elements, use the idQand xlmns:keyword. In my example, the Chart Tools.xlamand Priveleged UDFs.xlamboth have access to the elements with idQ's equal to x:chartToolsTaband x:privelgedUDFsTab. For this to work, the x:is required, and, I've defined its namespace in the first line of my customUI.xmlfile, <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="sao">. The section Two Ways to Customize the Fluent UIin the Microsoft guidegives some more details.
  2. If you want add-ins to access Ribbon elements shipped with Excel, use the isMSOkeyword. The section Two Ways to Customize the Fluent UIin the Microsoft guidegives some more details.
  1. 如果您希望加载项共享功能区元素，请使用idQandxlmns:关键字。在我的示例中，Chart Tools.xlamandPriveleged UDFs.xlam都可以访问idQ's 等于x:chartToolsTaband的元素x:privelgedUDFsTab。对于这项工作，将x:是必需的，而且，我在我的第一行定义了它的命名空间customUI.xml的文件，<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="sao">。Microsoft 指南中的两种自定义 Fluent UI 的方法部分提供了更多详细信息。
  2. 如果您希望加载项访问 Excel 附带的功能区元素，请使用isMSO关键字。Microsoft 指南中的两种自定义 Fluent UI 的方法部分提供了更多详细信息。

回答by John Korchok

Another approach to this would be to download Jan Karel Pieterse's free Open XML class module from this page: Editing elements in an OpenXML file using VBA

另一种方法是从此页面下载 Jan Karel Pieterse 的免费 Open XML 类模块：使用 VBA 编辑 OpenXML 文件中的元素

With this added to your VBA project, you can unzip the Excel file, use VBA to modify the XML, then use the class to rezip the files.

将此添加到您的 VBA 项目后，您可以解压缩 Excel 文件，使用 VBA 修改 XML，然后使用该类重新压缩文件。

相关推荐

Excel VBA - 如何有效地将多个单元格从一个文件复制到另一个文件？

Excel VBA - 如何有效地将多个单元格从一个文件复制到另一个文件？

vba 将逗号分隔的条目拆分为新行

vba 将逗号分隔的条目拆分为新行

vba 添加“你确定吗？” 到我的 excel 按钮，我该怎么办？

vba 添加“你确定吗？” 到我的 excel 按钮，我该怎么办？

vba 如何将数字四舍五入到最接近的十位？

vba 如何将数字四舍五入到最接近的十位？

相关推荐
最近更新
标签