当前位置： 文江博客 话题详情

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

发布于 2024-12-26 23:32:17 字数 1358 浏览 5 评论 0 原文

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

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

更新： 我尝试了此处的示例，但出现“需要对象”错误最后一条指令：

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
原文

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.

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..

UPDATE :
I tried this example from here but 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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（8）

靑春怀旧 2025-01-02 23:32:17

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

我使用这个名为 自定义 UI 编辑器< 的免费实用程序为我的 Excel 应用程序/加载项创建选项卡/a>.

为了详细解释以下内容，我创建了一个视频如何避免使用自定义 UI 编辑器创建您的第一个自定义选项卡。随意看看。

编辑：满足 OP 的新请求

教程

这是一个承诺的简短教程：

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

在此处输入图像描述

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

在此处输入图像描述

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

在此处输入图像描述

  1. 让我们检查一下代码

在此处输入图像描述

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

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

    <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](https://www.dropbox.com/s/8mvexoalel6icix/Office2007IconsGallery.EXE?dl=0).

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

演示

接下来，我们创建 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>

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

在此处输入图像描述

现在是代码部分。打开 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

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

在此处输入图像描述

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.

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

To explain the below in detail, I have created a video How to avoid Create Your First Custom Tab Using the Custom UI Editor. Feel free to have a look.

Edit: To accommodate new request by 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

  1. 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

  1. 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

  1. 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](https://www.dropbox.com/s/8mvexoalel6icix/Office2007IconsGallery.EXE?dl=0).

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

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:

<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:

enter image description here

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

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:

enter image description here

回复 原文
白鸥掠海 2025-01-02 23:32:17

我能够使用 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

在 Wookbook 打开事件中调用 LoadCustRibbon 子函数，并在 ThisWorkbook 代码文件的 Before_Close 事件中调用 ClearCustRibbon 子函数。

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.

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.

回复 原文
凉薄对峙 2025-01-02 23:32:17

我疯狂地挣扎，但这实际上是正确的答案。无论如何，我错过的是：

  1. 正如其他人所说，无法使用 VBA 创建 CustomUI 功能区，但是，您不需要这样做！
  2. 这个想法是您使用 Excel 的 File > 创建 xml Ribbon 代码选项>自定义功能区，然后将功能区导出到 .customUI 文件（它只是一个 txt 文件，其中包含 xml）
  3. 现在来了技巧：您可以包含 .customUI使用他们在此处引用的 MS 工具在您的 .xlsm 文件中编写代码，方法是从 .customUI 文件中复制代码
  4. 一旦将其包含在 .xlsm 文件中，每次打开它时，您定义的功能区都是
    已添加到用户的功能区 - 但请使用 功能区 startFromScratch="false" >否则您会丢失其余的丝带。退出工作簿时，功能区将被移除。
  5. 从这里开始就很简单了，创建您的功能区，从 .customUI 文件中复制特定于您的功能区的 xml 代码，并将其放置在包装器中，如上所示（... 您的 xml

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

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

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

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 include the .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
    added to 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...)

By the way the page that explains it on Ron's site is now at
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

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

回复 原文
鹤仙姿 2025-01-02 23:32:17

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

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

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

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

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.

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.

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.

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.

回复 原文
·深蓝 2025-01-02 23:32:17

除了 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

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

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 ThisWorkbook on the left side, then paste this code:

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!

回复 原文
仅此而已 2025-01-02 23:32:17

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

  1. 创建一个或多个您想要自定义其功能区的 Excel 文件。就我而言，我创建了两个 .xlam 文件：Chart Tools.xlam 和 Priveleged UDFs.xlam，以演示多个添加如何ins可以和Ribbon进行交互。
  2. 为刚刚创建的每个文件创建一个文件夹，文件夹名称任意。
  3. 在您创建的每个文件夹中，添加 customUI 和 _rels 文件夹。
  4. 在每个 customUI 文件夹中，创建一个 customUI.xml 文件。 customUI.xml 文件详细说明了 Excel 文件如何与功能区交互。 第 2 部分Microsoft 指南涵盖了customUI.xml 文件中的元素。

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

<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>

我的 Priveleged UDFs.xlam customUI.xml 文件> 如下所示

<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. 对于您在步骤 1 中创建的每个文件，在其文件名后添加 .zip 后缀。就我而言，我将 Chart Tools.xlam 重命名为 Chart Tools.xlam.zip，将 Privelged UDFs.xlam 重命名为 Priveleged UDFs .xlam.zip。
  2. 打开每个 .zip 文件，然后导航到 _rels 文件夹。将 .rels 文件复制到您在步骤 3 中创建的 _rels 文件夹。使用以下命令编辑每个 .rels 文件：文本编辑器。来自 微软指南

最终 元素与结束元素之间
元素，添加创建关系的行
文档文件和自定义文件之间。确保您
正确指定文件夹和文件名。

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

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

<?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>

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

<?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. 将每个 .zip 文件中的 .rels 文件替换为您在上一步中修改的 .rels 文件。
  2. 将您创建的 .customUI 文件夹复制并粘贴到 .zip 文件的主目录中。
  3. 从您创建的 Excel 文件中删除 .zip 文件扩展名。
  4. 如果您已创建 .xlam 文件，请返回 Excel，将它们添加到您的 Excel 加载项中。
  5. 如果适用，请在每个加载项中创建回调。在第 4 步中，我的按钮中有 onAction 关键字。 onAction 关键字表示，当触发包含元素时，Excel 应用程序将触发紧跟在 onAction 关键字后面的用引号引起来的子例程。这称为回调。在我的 .xlam 文件中，我有一个名为 CallBacks 的模块，其中包含了我的回调子例程。

回调模块

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

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

我的 Priveleged 的​​ CallBacks 模块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

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

这是我完成的示例的样子

Finished Product

一些结束提示

  1. 如果您希望加载项共享功能区元素，请使用 idQ 和 xlmns: 关键字。在我的示例中，Chart Tools.xlam 和 Priveleged UDFs.xlam 都可以访问 idQ 等于 的元素x:chartToolsTab 和 x:privelgedUDFsTab。为此，需要 x:，并且我已在 customUI.xml 文件的第一行定义了其命名空间，。 Microsoft 指南 提供了更多详细信息。
  2. 如果您希望加载项访问 Excel 附带的功能区元素，请使用 isMSO 关键字。 Microsoft 指南 提供了更多详细信息。

I encountered difficulties with Roi-Kyi Bryant's solution when multiple add-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 example to customising the ribbon using only Excel. Note, my solution is derived from the Microsoft guide.

  1. Create Excel file/files whose ribbons you want to customise. In my case, I've created two .xlam files, Chart Tools.xlam and Priveleged UDFs.xlam, to demonstrate how multiple add-ins can interact with the Ribbon.
  2. Create a folder, with any folder name, for each file you just created.
  3. Inside each of the folders you've created, add a customUI and _rels folder.
  4. Inside each customUI folder, create a customUI.xml file. The customUI.xml file details how Excel files interact with the ribbon. Part 2 of the Microsoft guide covers the elements in the customUI.xml file.

My customUI.xml file for Chart Tools.xlam looks like this

<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.xml file for Priveleged UDFs.xlam looks like this

<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 each file you created in Step 1, suffix a .zip to their file name. In my case, I renamed Chart Tools.xlam to Chart Tools.xlam.zip, and Privelged UDFs.xlam to Priveleged UDFs.xlam.zip.
  2. Open each .zip file, and navigate to the _rels folder. Copy the .rels file to the _rels folder you created in Step 3. Edit each .rels file with a text editor. From the Microsoft guide

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 Type="http://schemas.microsoft.com/office/2006/
  relationships/ui/extensibility" Target="/customUI/customUI.xml" 
  Id="customUIRelID" />

My .rels file for Chart Tools.xlam looks like this

<?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 .rels file for Priveleged UDFs looks like this.

<?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 .rels files in each .zip file with the .rels file/files you modified in the previous step.
  2. Copy and paste the .customUI folder you created into the home directory of the .zip file/files.
  3. Remove the .zip file extension from the Excel files you created.
  4. If you've created .xlam files, back in Excel, add them to your Excel add-ins.
  5. If applicable, create callbacks in each of your add-ins. In Step 4, there are onAction keywords in my buttons. The onAction keyword indicates that, when the containing element is triggered, the Excel application will trigger the sub-routine encased in quotation marks directly after the onAction keyword. This is known as a callback. In my .xlam files, I have a module called CallBacks where I've included my callback sub-routines.

CallBacks Module

My CallBacks module for Chart Tools.xlam looks like

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 CallBacks module for Priveleged UDFs.xlam looks like

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 guide defines all the callback signatures.

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 idQ and xlmns: keyword. In my example, the Chart Tools.xlam and Priveleged UDFs.xlam both have access to the elements with idQ's equal to x:chartToolsTab and x:privelgedUDFsTab. For this to work, the x: is required, and, I've defined its namespace in the first line of my customUI.xml file, <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="sao">. The section Two Ways to Customize the Fluent UI in the Microsoft guide gives some more details.
  2. If you want add-ins to access Ribbon elements shipped with Excel, use the isMSO keyword. The section Two Ways to Customize the Fluent UI in the Microsoft guide gives some more details.
回复 原文
思念满溢 2025-01-02 23:32:17

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

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

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

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.

回复 原文
画中仙 2025-01-02 23:32:17

这是一个适用于 Excel 365 的简单解决方案。

首先将此代码输入到空白工作簿中。

Option Explicit

Sub sbHelloWorld()
 MsgBox "Hello World!"
End Sub

这是基本自定义功能区的 XML。我使用记事本将此 XML 粘贴到名为“rb_HelloWorld.txt”的文件中，然后保存并将其复制到文件夹 -“C:\Users\david\AppData\Local\Microsoft\Office” 该文件夹一开始就被隐藏了，所以它需要取消隐藏。

<mso:customUI      xmlns:mso='http://schemas.microsoft.com/office/2009/07/customui'>
    <mso:ribbon>
        <mso:qat/>
        <mso:tabs>
        <mso:tab idQ="mso:TabDrawInk" visible="false"/>
        <mso:tab id="mso_c1.2A492F1" label="New Tab">
            <mso:group id="mso_c2.2A492F1" label="New Group" autoScale="true">
                <mso:button id="sbHelloWorld" label="sbHelloWorld" imageMso="ListMacros" onAction="sbHelloWorld" visible="true"/>
            </mso:group>
        </mso:tab>
        </mso:tabs>
    </mso:ribbon>
</mso:customUI>

这两个例程用于将新功能区复制到位，然后在工作簿退出时将其删除。

Sub sbCopyFile()
 Dim sOfficeUIDir As String
 Dim sHWFile As String
 Dim sUIFile As String
 Dim sTest As String
 sOfficeUIDir = "C:\Users\david\AppData\Local\Microsoft\Office\"
 sHWFile = sOfficeUIDir & "rb_HelloWorld.txt"
 sUIFile = sOfficeUIDir & "Excel.officeUI"
 sTest = Dir(sHWFile)
 If Not sTest = "" Then
  FileCopy sHWFile, sUIFile
 End If
End Sub



Sub sbDeleteFile()
 Dim sOfficeUIDir As String
 Dim sUIFile As String
 Dim sTest As String
 sOfficeUIDir = "C:\Users\david\AppData\Local\Microsoft\Office\"
 sUIFile = sOfficeUIDir & "Excel.officeUI"
 sTest = Dir(sUIFile)
 If Not sTest = "" Then
  Kill (sUIFile)
 End If
End Sub

可以使用内置过程 Workbook_Activate 和 Workbook_Deactivate 添加和删除自定义功能区。

Private Sub Workbook_Activate()
 Call sbCopyFile
End Sub

Private Sub Workbook_Deactivate()
 Call sbDeleteFile
End Sub

从自定义功能区上的按钮调用例程 sbHelloWorld()。

This is a simple solution which works in Excel 365.

Enter this code into a blank workbook first.

Option Explicit

Sub sbHelloWorld()
 MsgBox "Hello World!"
End Sub

This is the XML for a basic custom ribbon. I used Notepad to paste this XML into a file called "rb_HelloWorld.txt" then saved it and copied it to the folder - "C:\Users\david\AppData\Local\Microsoft\Office" This folder was hidden to begin with so it needs unhiding.

<mso:customUI      xmlns:mso='http://schemas.microsoft.com/office/2009/07/customui'>
    <mso:ribbon>
        <mso:qat/>
        <mso:tabs>
        <mso:tab idQ="mso:TabDrawInk" visible="false"/>
        <mso:tab id="mso_c1.2A492F1" label="New Tab">
            <mso:group id="mso_c2.2A492F1" label="New Group" autoScale="true">
                <mso:button id="sbHelloWorld" label="sbHelloWorld" imageMso="ListMacros" onAction="sbHelloWorld" visible="true"/>
            </mso:group>
        </mso:tab>
        </mso:tabs>
    </mso:ribbon>
</mso:customUI>

These are the two routines used to copy the new ribbon into place and then to delete it when the workbook exits.

Sub sbCopyFile()
 Dim sOfficeUIDir As String
 Dim sHWFile As String
 Dim sUIFile As String
 Dim sTest As String
 sOfficeUIDir = "C:\Users\david\AppData\Local\Microsoft\Office\"
 sHWFile = sOfficeUIDir & "rb_HelloWorld.txt"
 sUIFile = sOfficeUIDir & "Excel.officeUI"
 sTest = Dir(sHWFile)
 If Not sTest = "" Then
  FileCopy sHWFile, sUIFile
 End If
End Sub



Sub sbDeleteFile()
 Dim sOfficeUIDir As String
 Dim sUIFile As String
 Dim sTest As String
 sOfficeUIDir = "C:\Users\david\AppData\Local\Microsoft\Office\"
 sUIFile = sOfficeUIDir & "Excel.officeUI"
 sTest = Dir(sUIFile)
 If Not sTest = "" Then
  Kill (sUIFile)
 End If
End Sub

The custom ribbon can be added and removed using the built in procedures Workbook_Activate and Workbook_Deactivate.

Private Sub Workbook_Activate()
 Call sbCopyFile
End Sub

Private Sub Workbook_Deactivate()
 Call sbDeleteFile
End Sub

The routine sbHelloWorld() is called from the button on the custom ribbon.

回复 原文
~没有更多了~

关于作者

长发绾君心

暂无简介

文章
评论
28 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

alipaysp_snBf0MSZIv

文章 0 评论 0

梦断已成空

文章 0 评论 0

瞎闹

文章 0 评论 0

凯凯我们等你回来

文章 0 评论 0

寄意

文章 0 评论 0

似梦非梦

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文