Problem adding custom Group to existing Ribbon Tab in Excel 2007

[tylerc]

I am using VS 2008 to create a new Group for the Excel 2007 Ribbon. When
debugging in VS 2008, I am able to add it to any of the "default" Tabs (e.g.
Home). However, I want to add it to the Pivot Table Options tab
(TabPivotTableToolsOptions) - and this isn't working.

I suspect it has something to do with the fact that this tab doesn't appear
until after you've added a Pivot Table to a worksheet (perhaps I need to
capture some kind of event and then add my group?)

Does anyone have a suggestion on how to get this working?

 

[Joe]

Hello Tyler,

Boy, that VS 2008 Ribbon visual designer is easy to use for
customizing the core tabs, but when you want to customize contextual
tabs, it's a lot more work. I am still in the process of figuring it
out, so consider my suggestions a starting point for further
exploration rather than a specific set of cookbook instructions.

0) If you don't have it already, download the Custom UI Editor from
http://openxmldeveloper.org/articles/CustomUIeditor.aspx.
1) Read http://www2.mabat3.co.il/phpBB/uploads/oraCyVeFCy.pdf and note
the XML format for contextual tabs (in PowerPoint) on page 16.
2) Get the XML for your button right using the Custom UI Editor
3) In VS 2008, add New Ribbon XML (as opposed to Ribbon Visual
Designer) to your project

At this point, you are caught up to where I am. I haven't gotten it
working yet but belive myself to be on the right track. Please update
this thread with your results and I promise to do the same.

- Joe

 

[Joe]

Correction to the previous post - it's page 21 of the PDF in step 1
that demonstrates contextual tabs.

 

[tylerc]

Wow Joe - thanks for the replies. Maybe I will just stick to a custom Tab
(he he). If I get this working, I will let you know.

 

[Joe]

I found a more direct route to the solution.

1: How to export a ribbon from the designer to XML:
http://msdn2.microsoft.com/en-us/library/bb386297.aspx
2: How to write callbacks: http://msdn2.microsoft.com/en-us/library/aa942866.aspx

- Joe

 

[Joe]

Here is the simplified, VB 2008 way to add custom buttons to your
Office 2007 ribbon, including contextual tabs not handled by the
visual designer. This example will put a custom group with an upload
button on the Home tab in Excel, and a custom group with a download
button on the Pivot Table tab.

1) Write some VB code in ThisAddIn.vb that you want your custom ribbon
buttons to call.

Public Sub Upload()
msgbox("Going Up!")
End Sub

Public Sub Download()
msgbox("Going Down")
End Sub

2) Create custom images and save them in the project resource file. In
this example, assume we add two images, uparrow.png and downarrow.png.

From Project | Properties, select Resources
Change dropdown selection from Strings to Images
From Add Resource, select Add Existing File
[repeat for each image you need in the ribbon]

3) Add Ribbon XML to your project

Right-click the your project in Solution Explorer and select Add | New
Item...
From the Office group, select Ribbon (XML)

4) The Ribbon.vb module contains some preformatted advice. Follow Step
1 immediately - copy and paste the code block into your ThisAddIn
class.

5) Create the Image Handler callback that will load your custom ribbon
images

Expand the Ribbon Callbacks region
Add the following code insude the Callbacks region.

Public Function GetImage(ByVal imageName As String) As
System.Drawing.Bitmap
Select Case imageName
Case "UpArrow"
Return My.Resources.uparrow
Case "DownArrow"
Return My.Resources.downarrow
Case Else
Return Nothing
End Select
End Function

6) Create the callbacks that your buttons will use to launch your code

Inside the Callbacks region of Ribbon.vb, add the following code:

Public Sub btnDownload_Click(ByVal control As Office.IRibbonControl)
Globals.ThisAddIn.Download()
End Sub

Public Sub btnUpload_Click(ByVal control As OfficeIRibbonControl)Get
Globals.ThisAddIn.Upload()
End Sub

7) In Solution Explorer, switch to RibbonXML

8) Modify the customUI line to include the following attribute:
loadImage="GetImage"

The entire line will now look like this:
<customUI onLoad="Ribbon_Load" xmlns="http://schemas.microsoft.com/
office/2006/01/customui" loadImage="GetImage">

GetImage is the name of the callback function you created in step 5.

9) Add the custom group and Upload button to the Home tab

Modify the XML as shown below:

<tab idMso="TabHome">
<group id="MyGroup" label="My Group">
<button id="btnUpload" onAction="btnUpload_Click"
label="Upload" image="UpArrow"/>
</group>
</tab>

The TabHome idMSO is a keyword that I looked up in an Excel
spreadsheet from Microsoft. Get it here:
http://www.microsoft.com/downloads/...E9-4D11-46A5-898D-23E4F331E9AE&displaylang=en.

Everything is case sensitive, so if your GetImage callback function
refers to UPArrow, then you need to say UPArrow in the RibbonXML

10) Add the custom group to the PivotTable tab by adding the following
code inside the <ribbon></ribbon> collection, below the <tabs></tabs>
collection

<contextualTabs>
<tabSet idMso="TabSetPivotTableTools">
<tab idMso="TabPivotTableToolsDesign">
<group id="MyGroup2" label="My Group2">
<button id="buttonDownload" onAction ="btnDownload_Click"
label="Download" image="DownArrow"/>
</group>
</tab>
</tabSet>
</contextualTabs>

That's it - good luck!

Joe Stern
Philadelphia, PA

 

[tylerc]

Joe - This worked great - thanks again!
Tyler

Here is the simplified, VB 2008 way to add custom buttons to your
Office 2007 ribbon, including contextual tabs not handled by the
visual designer. This example will put a custom group with an upload
button on the Home tab in Excel, and a custom group with a download
button on the Pivot Table tab.

1) Write some VB code in ThisAddIn.vb that you want your custom ribbon
buttons to call.

Public Sub Upload()
msgbox("Going Up!")
End Sub

Public Sub Download()
msgbox("Going Down")
End Sub

2) Create custom images and save them in the project resource file. In
this example, assume we add two images, uparrow.png and downarrow.png.

From Project | Properties, select Resources
Change dropdown selection from Strings to Images
From Add Resource, select Add Existing File
[repeat for each image you need in the ribbon]

3) Add Ribbon XML to your project

Right-click the your project in Solution Explorer and select Add | New
Item...
From the Office group, select Ribbon (XML)

4) The Ribbon.vb module contains some preformatted advice. Follow Step
1 immediately - copy and paste the code block into your ThisAddIn
class.

5) Create the Image Handler callback that will load your custom ribbon
images

Expand the Ribbon Callbacks region
Add the following code insude the Callbacks region.

Public Function GetImage(ByVal imageName As String) As
System.Drawing.Bitmap
Select Case imageName
Case "UpArrow"
Return My.Resources.uparrow
Case "DownArrow"
Return My.Resources.downarrow
Case Else
Return Nothing
End Select
End Function

6) Create the callbacks that your buttons will use to launch your code

Inside the Callbacks region of Ribbon.vb, add the following code:

Public Sub btnDownload_Click(ByVal control As Office.IRibbonControl)
Globals.ThisAddIn.Download()
End Sub

Public Sub btnUpload_Click(ByVal control As OfficeIRibbonControl)Get
Globals.ThisAddIn.Upload()
End Sub

7) In Solution Explorer, switch to RibbonXML

8) Modify the customUI line to include the following attribute:
loadImage="GetImage"

The entire line will now look like this:
<customUI onLoad="Ribbon_Load" xmlns="http://schemas.microsoft.com/
office/2006/01/customui" loadImage="GetImage">

GetImage is the name of the callback function you created in step 5.

9) Add the custom group and Upload button to the Home tab

Modify the XML as shown below:

<tab idMso="TabHome">
<group id="MyGroup" label="My Group">
<button id="btnUpload" onAction="btnUpload_Click"
label="Upload" image="UpArrow"/>
</group>
</tab>

The TabHome idMSO is a keyword that I looked up in an Excel
spreadsheet from Microsoft. Get it here:
http://www.microsoft.com/downloads/...E9-4D11-46A5-898D-23E4F331E9AE&displaylang=en.

Everything is case sensitive, so if your GetImage callback function
refers to UPArrow, then you need to say UPArrow in the RibbonXML

10) Add the custom group to the PivotTable tab by adding the following
code inside the <ribbon></ribbon> collection, below the <tabs></tabs>
collection

<contextualTabs>
<tabSet idMso="TabSetPivotTableTools">
<tab idMso="TabPivotTableToolsDesign">
<group id="MyGroup2" label="My Group2">
<button id="buttonDownload" onAction ="btnDownload_Click"
label="Download" image="DownArrow"/>
</group>
</tab>
</tabSet>
</contextualTabs>

That's it - good luck!

Joe Stern
Philadelphia, PA