Summary: Learn how to customize Contextual tabs in Excel 2007 and Excel 2010

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 and Excel 2010
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 want to 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 and Excel 2010

• TabSetSmartArtTools
• TabSetChartTools
• TabSetDrawingTools
• TabSetPictureTools
• TabSetPivotTableTools
• TabSetHeaderAndFooterTools
• TabSetTableToolsExcel
• TabSetPivotChartTools
• TabSetInkTools
• TabSetSparkline (new in Excel 2010)
• TabSetSlicerTools (new in Excel 2010)
• 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 below it with a lot if information and options about tables.




2: Save and close the workbook now


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

If we want to customize the Table Tools tab we must add RibbonX to the workbook.
To add RibbonX to your workbook I suggest that you use the Custom UI Editor from Trang Luu.
http://openxmldeveloper.org/archive/2006/05/26/CustomUIeditor.aspx

If this is new for you, please first read the information on my Ribbon page:
http://www.rondebruin.nl/ribbon.htm


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
    Note: If there is no customUI14.xml file, it will load the customUI.xml file. This works in Excel 2010, to.

If you want to load different RibbonX for Excel 2007 and Excel 2010 read this page.
http://www.rondebruin.nl/ribbonx20072010.htm

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"

But how do you find this information
Visit this page: http://www.rondebruin.nl/ribbon.htm#names

To use another images (imageMso) for your buttons visit:
http://www.rondebruin.nl/ribbon.htm#images


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

12: Press Alt q to close the VBA editor
13: Save TestWorkBook.xlsm
14: Test the buttons in the two groups


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



To create your own tab next to the Design tab we must 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"



More information

Change the Ribbon in Excel 2007 or Excel 2010
http://www.rondebruin.nl/ribbon.htm

Customizing the 2007 Office Fluent Ribbon for Developers (MSDN)
http://msdn2.microsoft.com/en-us/library/aa338202.aspx


For working with Tables see

Overview of Excel tables
http://office.microsoft.com/en-us/excel/HA100485461033.aspx

Working with Lists and Tables: VBA Samples (Part 1 of 2)
http://msdn.microsoft.com/en-us/library/dd637097(office.11).aspx

Working with Lists and Tables: VBA Samples (Part 2 of 2)
http://msdn.microsoft.com/en-us/library/dd795215(office.11).aspx

Table Tools Add-in for Excel 2007-2010
http://www.rondebruin.nl/table.htm

Jan Karel Pieterse (MVP) :
http://www.jkp-ads.com/Articles/Excel2007Tables.asp
http://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp

Frank Rice : Bringing Improvements to Tables in Excel 2007 (Part 1 of 6)
http://msdn.microsoft.com/en-us/library/bb693324(office.11).aspx
See the links for part 2 -6 in the MSDN menu