Creating a custom tab in the Excel ribbon

on

In this guide I will show you how to create a custom tab in Microsoft Excel, the tab will show in the ribbon just like the built-in tabs and once we get to the end I'll show you how to make the tab open with Excel when it starts up.

I have confirmed that these custom tabs still work in Microsoft Office 2016 so if you've made any custom tabs or plan on making any custom tabs rest assured that they are still supported in modern versions of Office.

Creating your own ribbon is especially useful in organisations where you repeatedly need to complete the same tasks, a custom tab allows you to integrate your macros into a user-friendly menu allowing staff to complete their daily tasks without hassle.

To complete this task you should be familiar with Excel, Visual Basic, and how to create macros in Excel.

The process for creating custom ribbon tabs in Microsoft Word is slightly different and I endeavour to create a guide on how to do this in Word and will link it here when I do.



  1. To start creating a custom tab in Excel create a new Excel Workbook and save it as a macro-enabled format (.xlsm) 
  2. Access the Visual Basic editor by pressing Alt+F11 and Import the macros into the project either using the import module function, or by creating a new module and pasting in the code, ensure the macros are in the project workbook and not the PERSONAL.XLSB file.  
  3. For each of your macros you also need to create a callback for the buttons in the custom tab to work, you can adapt the following code for your macros.

    'Callback for btnYourFunction_Click onAction
    Sub btnYourFunction_Click(control As IRibbonControl)
    YourFunction
    End Sub


     
  4. Once all that’s done save changes and close the workbook 
  5. Now we need to open the CustomUI Editor, this is a small program that allows us to create the custom tab and the elements inside it e.g. buttons, labels .etc, the CustomUI Editor can be downloaded from http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/06/7293.aspx
  6. Open CustomUI Editor and click the open file button and select the excel workbook.
  7. The file should appear in the left pane, right-click it and select Office 2010 UI Part  
  8. A customUI14.xml will then appear, click on this file. 
  9. Next we'll get the program to insert the basic XML for us. Click Insert > Sample XML > Custom Tab
  10. You can then adapt this XML to the controls you need in the ribbon, note the buttons onAction needs to be set to the callback function, not the function of your macro.
  11. Finally we need to reopen the workbook in Excel and save the file in a different file format, go to File > Save & Send > Change File Type and click "Save As" 
  12. From the Filetype dropdown select "Excel Add-In (*.xlam) 
  13. The file is now ready to copy into the Excel startup folder. Copy the .xlam file into "%USERPROFILE%\Application Data\Microsoft\Excel\XLStart\"
  14. Now if you open Excel your custom tab will appear.
If you are still stuck trying to work out the XML the following illustration will hopefully be helpful.

Extra resources

A complete listing of all the elements that are available for use are listed here: https://msdn.microsoft.com/en-us/library/dd926139(v=office.12).aspx
A complete listing of the different button icons (imageMso) are available at https://msdn.microsoft.com/en-us/library/dd953682(v=office.12).aspx

Was this helpful?

Yes No


Comments

Post a Comment