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     lt mso customUI xmlns mso   http   schemas microsoft com office 2009 07 customui   gt   ribbonXml   ribbonXml       lt mso ribbon gt   ribbonXml   ribbonXml         lt mso qat  gt   ribbonXml   ribbonXml         lt mso tabs gt   ribbonXml   ribbonXml           lt mso tab id   highlightTab   label   Highlight   insertBeforeQ   mso TabFormat   gt   ribbonXml   ribbonXml             lt mso group id   testGroup   label   Test   autoScale   true   gt   ribbonXml   ribbonXml               lt mso button id   highlightManualTasks   label   Toggle Manual Task Color     ribbonXml   ribbonXml    imageMso   DiagramTargetInsertClassic   onAction   ToggleManualTasksColor    gt   ribbonXml   ribbonXml             lt  mso group gt   ribbonXml   ribbonXml           lt  mso tab gt   ribbonXml   ribbonXml         lt  mso tabs gt   ribbonXml   ribbonXml       lt  mso ribbon gt   ribbonXml   ribbonXml     lt  mso customUI gt    ActiveProject SetCustomUI  ribbonXml  End Sub

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.

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   lt customUI xmlns  http   schemas microsoft com office 2006 01 customui  xmlns x  sao  gt     lt ribbon gt       lt tabs gt         lt tab idQ  x chartToolsTab  label  Chart Tools  gt           lt group id  relativeChartMovementGroup  label  Relative Chart Movement   gt               lt button id  moveChartWithRelativeLinksButton  label  Copy and Move  imageMso  ResultsPaneStartFindAndReplace  onAction  MoveChartWithRelativeLinksCallBack  visible  true  size  normal   gt               lt button id  moveChartToManySheetsWithRelativeLinksButton  label  Copy and Distribute  imageMso  OutlineDemoteToBodyText  onAction  MoveChartToManySheetsWithRelativeLinksCallBack  visible  true  size  normal   gt           lt  group  gt           lt group id  chartDeletionGroup  label  Chart Deletion  gt               lt button id  deleteAllChartsInWorkbookSharingAnAddressButton  label  Delete Charts  imageMso  CancelRequest  onAction  DeleteAllChartsInWorkbookSharingAnAddressCallBack  visible  true  size  normal   gt           lt  group gt         lt  tab gt       lt  tabs gt     lt  ribbon gt   lt  customUI gt    My customUI xml file for Priveleged UDFs xlam looks like this   lt customUI xmlns  http   schemas microsoft com office 2006 01 customui  xmlns x  sao  gt     lt ribbon gt       lt tabs gt         lt tab idQ  x privelgedUDFsTab  label  Privelged UDFs  gt           lt group id  privelgedUDFsGroup  label  Toggle   gt               lt button id  initialisePrivelegedUDFsButton  label  Activate  imageMso  TagMarkComplete  onAction  InitialisePrivelegedUDFsCallBack  visible  true  size  normal   gt               lt button id  deInitialisePrivelegedUDFsButton  label  De-Activate  imageMso  CancelRequest  onAction  DeInitialisePrivelegedUDFsCallBack  visible  true  size  normal   gt           lt  group  gt         lt  tab gt       lt  tabs gt     lt  ribbon gt   lt  customUI gt     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  lt Relationship gt  element and the closing    lt Relationships gt  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     lt Relationship Type  http   schemas microsoft com office 2006    relationships ui extensibility  Target   customUI customUI xml     Id  customUIRelID    gt    My  rels file for Chart Tools xlam looks like this   lt  xml version  1 0  encoding  UTF-8  standalone  yes   gt       lt Relationships xmlns  http   schemas openxmlformats org package 2006 relationships  gt           lt Relationship Id  rId3  Type  http   schemas openxmlformats org officeDocument 2006 relationships extended-properties  Target  docProps app xml   gt  lt Relationship Id  rId2  Type  http   schemas openxmlformats org package 2006 relationships metadata core-properties  Target  docProps core xml   gt           lt Relationship Id  rId1  Type  http   schemas openxmlformats org officeDocument 2006 relationships officeDocument  Target  xl workbook xml   gt           lt Relationship Type  http   schemas microsoft com office 2006 relationships ui extensibility  Target   customUI customUI xml  Id  chartToolsCustomUIRel    gt       lt  Relationships gt    My  rels file for Priveleged UDFs looks like this    lt  xml version  1 0  encoding  UTF-8  standalone  yes   gt       lt Relationships xmlns  http   schemas openxmlformats org package 2006 relationships  gt           lt Relationship Id  rId3  Type  http   schemas openxmlformats org officeDocument 2006 relationships extended-properties  Target  docProps app xml   gt  lt Relationship Id  rId2  Type  http   schemas openxmlformats org package 2006 relationships metadata core-properties  Target  docProps core xml   gt           lt Relationship Id  rId1  Type  http   schemas openxmlformats org officeDocument 2006 relationships officeDocument  Target  xl workbook xml   gt           lt Relationship Type  http   schemas microsoft com office 2006 relationships ui extensibility  Target   customUI customUI xml  Id  privelegedUDFsCustomUIRel    gt       lt  Relationships gt     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   lt customUI xmlns  http   schemas microsoft com office 2006 01 customui  xmlns x  sao  gt   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

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

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

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

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

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

Examples related to excel

Examples related to vba

Examples related to excel-2007

Examples related to ribbonx