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

问题描述

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

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

更新:我从 .

onAction="Callback":Callback"是当你点击按钮时运行的程序的名称.

演示

这样，让我们​​创建 2 个按钮并将它们命名为JG Button 1"和JG Button 2".让我们保持快乐的脸作为第一个形象，让我们保持太阳"作为第二个形象.修改后的代码现在看起来像这样:

<ribbon startFromScratch="false"><标签><tab id="MyCustomTab" label="Jerome" insertAfterMso="TabView"><group id="customGroup1" label="第一个标签"><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"/></组></tab></tabs></色带></customUI>

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

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

Public Sub Callback1(control As IRibbonControl)MsgBox "你按下了笑脸"结束子Public Sub Callback2(control As IRibbonControl)MsgBox "你按下了太阳"结束子

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

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

---

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.

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

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.

4. Let's inspect the code

   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.

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:

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:

Hope this helps!

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