How to add a custom Ribbon tab using VBA

Question

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

User · Answer

I struggled like mad  but this is actually the right answer  For what it is worth  what I missed was is this    As others say  one can t create the CustomUI ribbon with VBA  however  you don t need to  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  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 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  lt  ribbon startFromScratch  false    or you lose the rest of the ribbon  On exit-ing the workbook  the ribbon is removed  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      lt  tabs   your xml  lt   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

User · Answer

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.

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.
Create a folder, with any folder name, for each file you just created.
Inside each of the folders you've created, add a customUI and _rels folder.
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>

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

Replace the .rels files in each .zip file with the .rels file/files you modified in the previous step.
Copy and paste the .customUI folder you created into the home directory of the .zip file/files.
Remove the .zip file extension from the Excel files you created.
If you've created .xlam files, back in Excel, add them to your Excel add-ins.
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.

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

Some closing tips

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

User · Answer

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.

User · Answer

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    amp  user  amp    AppData Local Microsoft Office   fileName    Excel officeUI   ribbonXML     lt mso customUI      xmlns mso  http   schemas microsoft com office 2009 07 customui  gt    amp  vbNewLine ribbonXML   ribbonXML       lt mso ribbon gt    amp  vbNewLine ribbonXML   ribbonXML         lt mso qat  gt    amp  vbNewLine ribbonXML   ribbonXML         lt mso tabs gt    amp  vbNewLine ribbonXML   ribbonXML           lt mso tab id  reportTab  label  My Actions  insertBeforeQ  mso TabFormat  gt    amp  vbNewLine ribbonXML   ribbonXML             lt mso group id  reportGroup  label  Reports  autoScale  true  gt    amp  vbNewLine ribbonXML   ribbonXML               lt mso button id  runReport  label  Trim     amp  vbNewLine ribbonXML   ribbonXML    imageMso  AppointmentColor3       onAction  TrimSelection   gt    amp  vbNewLine ribbonXML   ribbonXML             lt  mso group gt    amp  vbNewLine ribbonXML   ribbonXML           lt  mso tab gt    amp  vbNewLine ribbonXML   ribbonXML         lt  mso tabs gt    amp  vbNewLine ribbonXML   ribbonXML       lt  mso ribbon gt    amp  vbNewLine ribbonXML   ribbonXML     lt  mso customUI gt    ribbonXML   Replace ribbonXML             Open path  amp  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    amp  user  amp    AppData Local Microsoft Office   fileName    Excel officeUI   ribbonXML     lt mso customUI           xmlns mso   http   schemas microsoft com office 2009 07 customui   gt    amp      lt mso ribbon gt  lt  mso ribbon gt  lt  mso customUI gt    Open path  amp  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

User · Answer

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.

User · Answer

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    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    Right click as shown in the image below and click on  Office 2007 Custom UI Part   It will insert the  customUI 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    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     lt button id  customButton  label  Custom Button  imageMso  HappyFace  size  large  onAction  Callback    gt    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    lt customUI xmlns  http   schemas microsoft com office 2006 01 customui  gt   lt ribbon startFromScratch  false  gt   lt tabs gt   lt tab id  MyCustomTab  label  Jerome  insertAfterMso  TabView  gt   lt group id  customGroup1  label  First Tab  gt   lt button id  customButton1  label  JG Button 1  imageMso  HappyFace  size  large  onAction  Callback1    gt   lt button id  customButton2  label  JG Button 2  imageMso  PictureBrightnessGallery  size  large  onAction  Callback2    gt   lt  group gt   lt  tab gt   lt  tabs gt   lt  ribbon gt   lt  customUI gt    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

User · Answer

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.

[excel] How to add a custom Ribbon tab using VBA?

The answer is

Examples related to excel

Examples related to vba

Examples related to excel-2007

Examples related to ribbonx

Tags