Customize Contextual tabs in Excel 2007-2016

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

---

Summary: Learn how to customize Contextual tabs in Excel 2007-2016

Overview of Contextual tabs in Excel

Contextual tabs are only visible when an object is selected. For example if the active cell is in a Table you see a Tab named Table Tools and if you select a picture you see a tab named Picture Tools in the Ribbon. These contextual tabs only contain information or options pertinent to that object only.

Customize Contextual tabs in Excel 2007-2016

To customize contextual tabs we use the same ribbon extensibility (RibbonX) model that you use to customize the other components of the Microsoft Office Fluent User Interface to include the ribbon and the Backstage view.

Before we start we first must know the RibbonX name of the contextual tab set that we want to change. These are the contextual tab sets that exist in Excel 2007-2016:

• TabSetSmartArtTools
• TabSetChartTools
• TabSetDrawingTools
• TabSetPictureTools
• TabSetPivotTableTools
• TabSetHeaderAndFooterTools
• TabSetTableToolsExcel
• TabSetPivotChartTools
• TabSetInkTools
• TabSetSparkline (new in Excel 2010)
• TabSetSlicerTools (new in Excel 2010)
• TabSetTimeSlicerTools(new in Excel 2013)
• TabSetEquationTools (new in Excel 2010)

I think the names are self-explaining and for this article I choose to use the contextual tab set that is visible when the active cell is in a Table. The name of this tab set is TabSetTableToolsExcel.

Let’s start

1: Open a new workbook and save it as TestWorkbook.xlsm (Excel Macro-Enabled workbook)

Then we enter some data like this:

Then we use Insert >Table on the Ribbon to make a Table, looks like this when you do it correct

If your active cell is in a table you see the Table Tools contextual tab set in the Ribbon with the Design tab active with a lot if information and options about tables.

2: Save and close the workbook now

 

Add your own groups in the design tab in the Table Tools tab set

If you want to insert RibbonX into a Excel 2007-2016 workbook to change the Ribbon, then I suggest you download and install the free Custom UI Editor to make this a lot easier : http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2006/05/26/customuieditor.aspx

Note: It is not possible to insert RibbonX into Excel 97-2003(xls) files.

3: Open TestWorkBook.xlsm in the Custom UI Editor
4: Right click on the File name in the Custom UI Editor or use the Insert menu
5: Choose "Office 2007 Custom UI Part" to create the customUI.xml file
6: Paste the RibbonX below in the right window

If you want to load different RibbonX for Excel 2007 and Excel 2010-2016 because you want to edit a tabset new in Excel 2010-2016, visit this page to learn how to use the Office 2007 Custom UI Part.

The RibbonX below will add two groups to the Design tab.
One with the built-in filter button and one with two custom macro buttons.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

  <ribbon>
    <contextualTabs>

      <tabSet idMso="TabSetTableToolsExcel" >
 
	<tab idMso="TabTableToolsDesignExcel" >
	<group id="TableToolsGroup1" label="Filter On/Off" 
			insertBeforeMso="GroupTableProperties">

          	<toggleButton idMso="Filter" size="large" /> 

	</group>

 	<group id="TableToolsGroup2" label="My Table Tools"  
			insertBeforeMso="GroupTableProperties" >

          	<button id="customButton1" label="My Super Macro 1" 
		size="large" onAction="RDB_Macro1" imageMso="AppointmentColor1"
		supertip="Information that is visible for My Super Macro 1."/>

          	<button id="customButton2" label="My Super Macro 2" 
		size="large" onAction="RDB_Macro2" imageMso="AppointmentColor2" 
		supertip="Information that is visible for My Super Macro 2." />
	</group>
	</tab>

      </tabSet>

    </contextualTabs>
  </ribbon>

</customUI>

Note: you see that I use a few other idMso’s in the xml
tab idMso="TabTableToolsDesignExcel"
insertBeforeMso="GroupTableProperties"

Also what to do if I want to use another image (imageMso)
Click on the bottom of this page on the link Ribbon Examples files and Tips

7: Click on the Save button in the Custom UI Editor and close the editor.
8: Open TestWorkBook.xlsm in Excel
9: Press Alt F11 to open the VBA editor
10: Use Insert > Menu to create a code module
11: Paste the two macros (callbacks) below in the module

'Callback for customButton1 onAction
Sub RDB_Macro1(control As IRibbonControl)
    MsgBox "This is Super Macro 1"
End Sub

'Callback for customButton2 onAction
Sub RDB_Macro2(control As IRibbonControl)
    MsgBox "This is Super Macro 2"
End Sub

 

How do you add your own tab in the Table Tools tab set?

To create your own tab next to the Design tab follow the steps below to change a few things in the xml of the example above.

1: Close TestWorkBook.xlsm in Excel
2: Open TestWorkBook.xlsm in the Custom UI Editor

Change
<tab idMso="TabTableToolsDesignExcel" >

To
<tab id="MyTableTab" label="My Table Tools">

And remove this in the lines that add the groups to the custom tab, do not remove the > after it.
insertBeforeMso="GroupTableProperties"

3: Click on the Save button in the Custom UI Editor and close the editor.
4: Open TestWorkBook.xlsm in Excel and test the buttons in the new tab named "My Table Tools"

 

Ribbon Examples files and Tips

Visit this page for tips and more examples : Ribbon Examples files and Tips

 

Copyright © 2022 Ron de Bruin

Site designed by Spider Web Woman Designs