如何使用VBA添加自定义功能区选项卡？ [英] How to add a custom Ribbon tab using VBA?

问题描述

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

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.

什么是快速简单的方法？当我的VBA加载到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..

更新：
我尝试过来自此处的示例，但在最后一条指令中收到需要的对象错误：

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

推荐答案

AFAIK您无法使用VBA Excel在Excel功能区中创建自定义选项卡。然而，您可以使用VBA隐藏/显示功能区组件。此外，您上面提到的链接是MS Project而不是MS 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.

我使用这个免费的实用程序名为为我的Excel应用程序/加载项创建选项卡href =http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx =noreferrer>自定义UI编辑器。

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

编辑：适应OP的新请求

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

Here is a short tutorial as promised:

1. 已经安装了自定义UI编辑器（CUIE），打开它，然后单击文件|打开并选择相关的Excel文件。在通过CUIE打开之前，请确保关闭Excel文件。我正在使用全新的工作表作为例子。

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.

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

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

下一步点击菜单插入|示例XML |自定义标签您将注意到基本代码是自动生成的。现在您都可以根据您的要求进行编辑。

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.

我们来检查代码

label =自定义选项卡：将自定义选项卡替换为要为其选项卡添加的名称。暂时让我们称之为Jerome。

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 ：这将是显示在按钮上。 HappyFace是你现在看到的。 您可以在此下载更多图片ID

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 =回调：回调是运行的过程的名称当您点击按钮时。

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

演示

这样，我们创建2个按钮，并将其称为JG按钮1和JG按钮2。让我们保持幸福的脸像第一个的形象，让我们保持太阳第二。修改后的代码现在如下所示：

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>

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

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:

现在的代码部分。打开VBA编辑器，插入一个模块并粘贴这段代码：

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

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

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:

希望这有帮助！

这篇关于如何使用VBA添加自定义功能区选项卡？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！