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 

Answer 1

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!

Answer 2

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.