Summary: Learn how to customize Contextual tabs in Excel 2007-2016
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.
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
• TabSetSparkline (new in Excel 2010)
• TabSetSlicerTools (new in Excel 2010)
• TabSetTimeSlicerTools(new in Excel 2013)
• TabSetEquationTools (new in Excel 2010)
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
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
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
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
<tab idMso="TabTableToolsDesignExcel" >
<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.
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"
Visit this page for tips and more examples : Ribbon Examples files and Tips