Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
22. February 2010 10:23

In our previous installment, we went through adding a Custom Task Pane to Excel to host the user interface of our VSTO add-in. However, we left off with one problem to solve. The task pane is shown when the add-in starts up, but if the user closes it, there is no mechanism to show it again. We will resolve that problem by using the ribbon, adding a button that restores the task pane visibility.

First, we will create a new folder in our project, called “Ribbon”. Right-click the folder, select “Add > New Item”, and pick “Ribbon (Visual Designer)” from the available templates. We will call our ribbon “AnakinRibbon”.

By now, your solution should look like this:

Visual Studio displays a visual interface, representing the ribbon we will use for Anakin:

By default, the ribbon comes pre-populated with a tab called “TabAddIns”, labeled Built-In. This reflects the fact that, by default, your add-in ribbon will show up in the standard Add-Ins tab of the ribbon.

While this would be perfectly acceptable, we actually want to add our add-in to an existing Ribbon tab, the “Review” tab. It seems like a natural place to find functionality related to comparing different versions of a spreadsheet, and this way, we can avoid crowding the Ribbon with new tabs, and integrate seamlessly with Office, without minimal disturbance to the user experience.

First, we will add a new Tab to our Ribbon. Expanding the Office Ribbon Controls section in the Toolbox reveals the set of controls we can use, most of them familiar.

Grab the Tab control, and drag it over the Ribbon (or right-click the Ribbon, and select Add Ribbon Tab). A new tab shows up, which is not marked as built-in. If you hit F5 at that point, you will see two things: the Add-Ins tab now contains an empty group (the group that is created by default), and a new tab has been created for us, where we could add controls if we wanted to use a custom tab.

First let’s hook up the new tab, so that its contents show up in the Review tab, instead of inside a new one. To do this, we need to provide the Tab with the Id of the built-in Excel tab it will be hosted in. Select the tab, and in the Properties window, modify the ControlIdType from “Custom” to “Office”, to indicate you want to use a built-in office tab, and type in “TabReview” in the OfficeId field.

Once this is done, the display of the Tab will change to TabReview (Built-In), indicating that the Ribbon recognized what we wanted to do. Each control built in the Ribbon has an Id, which you need to use if you want to access it – the complete list, for all Office applications, can be found here.

Next, let’s create a Group for our add-in, change its Label to Anakin, and drag a Button to the group, which we will label “Compare”.

Hit F5 to debut the project, you should see something like this, with the Review tab now containing our group and button.

Now let’s add some code, so that when the button is clicked, the Custom Task Pane visibility is set to true. First, we need to make the TaskPane property accessible, so we change the corresponding property on the ThisAddIn class from private to internal:

public partial class ThisAddIn
{

{
get
{
}
}

On the Ribbon, double-click on the button. An empty event handler is created for you – let’s add the following code to it:

private void ShowAnakin_Click(object sender, RibbonControlEventArgs e)
{
}

The Globals class exposes an internal static property ThisAddin, which provides access to the Add-In from anywhere within the add-in solution. We use it to navigate to the TaskPane, and make it visible whenever the button is clicked.

Let’s make a small modification, so that by default the task pane is hidden:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
}

The last thing we have to do is to remove the default add-in tab and its group from the ribbon. Open the AnakinRibbon designer, right-click on the AddInTab area, and delete – and we are done.

Now that our hooks are in place, we can begin to add some real functionality to our add-in. In the next installment, we will work on adding a tree view to the Custom Task Pane, so that the user can select which of the currently open worksheets he/she wants to run a comparison against. We will use WPF for that control – because the WPF tree view control is great, and because the ability to use WPF in Office Applications is a fantastic feature!

## Resources

List of the built-in ribbon tabs

2/27/2010 1:21:45 AM #

5/3/2010 5:22:05 PM #

I do like your theme here. Great and excellent sources of ideas and information. Please keep on sharing!

11/26/2010 3:15:23 AM #

I'd change the button proc to this:

12/20/2010 4:29:16 PM #

Create an Excel 2007 VSTO add-in: display open worksheets in a TreeView

Create an Excel 2007 VSTO add-in: display open worksheets in a TreeView

5/10/2011 7:55:46 AM #

When I press the button the debugger stops and makes yellow the line:
and the error "NullReferenceException was unhandled by user code"
'Object reference not set to an instance of an object'
What I did wrong?

5/10/2011 8:05:20 AM #

the word 'taskPane' is green underlined and the next message is displayed when the mouse is on it:
This line of code is inside thisAddin class like this:
{
{

{
get
{
}
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
var messageBox = MessageBox.Show("Salutari din Comm Addin (C#)!");
}

5/15/2011 3:07:37 AM #

Mathias

12/17/2011 8:03:46 PM #

5/25/2012 7:56:16 AM #

This really is a magnificent series.  You've made short work of a subject that seemed pretty abstruse to me.

Thanks much for the excellent work.

9/28/2012 12:25:32 AM #

Another great post, I appreciate all the work you put into this site, helping out others with your fun and creative works.

10/16/2012 4:12:12 AM #

I am developing an AddIn which adds a group to the Ribbon. Although it is an Application AddIn, I only wish to add to the Ribbon on certain Workbooks. For all other Workbooks, I do not want the AddIn to have any effect on the Ribbon (or anything else for that matter - but the Ribbon is the most important).
Question: How do I prevent the modification of the Ribbon except on certain Workbooks?

10/17/2012 10:04:34 AM #

Conceptually, in general, having a Ribbon which depends on the Workbook goes a bit against the grain of what an Add-In is - the Add-in is an extension of Excel, the application, and is present regardless of what document is open.
That being said, what you describe is feasible (I have done it). One question is how would your add-in know when to be active or not, i.e. when should the Ribbon be visible or hidden. In my case, the Add-In actually creates a unique Workbook which it controls, so that is easy. Otherwise, you would probably have to have a marker in the Workbook itself (maybe using CustomDocumentProperties msdn.microsoft.com/.../...perties(v=vs.100).aspx), and having the Add-In subscribe to Workbooks being opened or closed, and/or windows being activated/deactivated.
Hope this helps!
Mathias

12/14/2012 2:05:45 AM #

I am also trying to write an add-in where the tab is only visible on certain workbooks.  Could you please further clarify how this can be done?
How does creating a unique workbook control the ribbon visibility? Does the workbook that the add-in creates need to be hidden or otherwise protected?
I have played around with workbook activate events to try and make the add-in refresh the ribbon and refresh the ribbon based off of the document name or other property, but have been unsuccessful.
The workbooks that I want the tab to show up on need to be .xlsx files, not .xlsm.
Any help is appreciated!

12/14/2012 3:00:00 PM #

This is how I did it. It may well not be the best way, but in my very limited case, it works.
Protected Overrides Function CreateRibbonObjects() As IRibbonExtension()
If MyRibbon Is Nothing Then
MyRibbon = New Ribbon1
End If
Return New IRibbonExtension() {MyRibbon}
End Function
This simply creates MyRibbon and sets my added group's visibility to False.
Public Sub WB_Open(WB As Microsoft.Office.Interop.Excel.Workbook) Handles Application.WorkbookOpen
Dim WBOpening As New AWorkbook(WB)
If WBOpening.IsValid Then
If Not WBDict.ContainsKey(WB) Then
End If
CurrentWorkBook = WBOpening
If AccDB Is Nothing Then AccDB = New DataAccess
'TestFindVal()
Else
End If
End Sub
The only critical part is the creation of an instance of the AWorkbook Class. That is where I examine the Workbook that is opening to test if the Workbook is one that the Addin is useful for. If so, that instance is has IsValid set to True, otherwise it will be False.
If the workbook is valid, then my added group will be Visible, if not it will be not Visible.
I do nothing special to mark Valid workbooks. If a workbook is seen in the AWorkbook constructor to have  Worksheets named with certain names, then it is considered Valid. A Workbook must have several Worksheets with names built into the Addin to pass this test.
If this sounds like a hack, it is because it is!  There must be a better way, usable for production projects, but I never got around to finding that better way. This whole thing should have been a Document level application rather than an Addin. However, the development of a Document level project can be very awkward and error-prone in cases where you anticipate making later changes to the code.
All of this is dealt with in a forum thread found at: social.msdn.microsoft.com/.../...220-5da76fd28563.

12/14/2012 3:07:34 PM #

In the last post, I forgot to mention that you also have to add code to deal with Workbook Activation. This code is also added to ThisAddIn:
Public Sub WB_Activate(WB As Microsoft.Office.Interop.Excel.Workbook) Handles Application.WorkbookActivate
Debug.WriteLine("WB Activate: " & WB.Path & "\" & WB.Name)
CurrentWorkBook = Nothing
If WBDict.ContainsKey(WB) Then
CurrentWorkBook = WBDict(WB)
Else
Dim WBOpening As New AWorkbook(WB)
If WBOpening.IsValid Then
If Not WBDict.ContainsKey(WB) Then
End If
CurrentWorkBook = WBOpening
If AccDB Is Nothing Then AccDB = New DataAccess
Else
End If
End If
End Sub

12/17/2012 6:45:17 AM #

Thank you for your help.  I was able to find another solution for my particular case, since I have only a handful of workbooks that I want the add-in to appear in, rather than include code in the add-in to decide when it needs to be visible, I changed the .xml code for the handful of workbooks that I do want the add-in to appear in.  Then I eliminated all of the CustomUI code from the add-in itself.
So, I made the add-in have all of the execution code for the custom functions, but excluded the interface to access any of the code.
Then, I modified the .xml code in the individual .xlsx (NOT macro-enabled) workbooks that I wanted the custom functions to appear in to create a custom tab, group, and buttons for the add-in.

6/13/2013 10:26:42 PM #

Hello and thank you for this tutorial. I am sure as of today this is the best one available  I found after 2 days of researching!
I have found a problem and was able to solve it while trying to follow this tutorial for adding a Ribbon (Excel 2010, VS2012). When you add a new Item straight to the Ribbon folder you may be getting an error trying while you are trying to compile and run your project.
You can find the the complete problem description and solution here: stackoverflow.com/.../an-excel-ribbon-via-vsto-solution-explorer-folder-structure-vs-path-in-code

6/13/2013 11:01:33 PM #

I think it would be nice to add this code with some description to the above tutorial:

private void button1_Click(object sender, RibbonControlEventArgs e)
{
{
}
else
{
}
}

• Comment
• Preview

#### Need help with F#?

The premier team for
F# training & consulting.