Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 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:

RibbonFolder

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

EmptyRibbon

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.

AvailableRibbonControls

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.

ExtraTab

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.

 BuiltInTab

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.

UsingBuiltInRibbonTab

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”.

RibbonWithButton

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

AddInWithButton 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
{
    private CustomTaskPane taskPane;

    internal CustomTaskPane TaskPane
    {
        get
        {
            return this.taskPane;
        }
    }

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)
{
    Globals.ThisAddIn.TaskPane.Visible = true;
}

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)
{
    var taskPaneView = new TaskPaneView();
    this.taskPane = this.CustomTaskPanes.Add(taskPaneView, "Anakin");
    this.taskPane.Visible = false;
}

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

Comments

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

trackback

Create an Excel 2007 VSTO add-in: adding a WPF control

Create an Excel 2007 VSTO add-in: adding a WPF control

Clear Lines Blog | Reply

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

Rheology modifiers

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

Rheology modifiers United States | Reply

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

patricio cuaron

I'd change the button proc to this:
lobals.ThisAddIn.CambiosTaskPane.Visible = Not Globals.ThisAddIn.CambiosTaskPane.Visible

patricio cuaron Argentina | Reply

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

trackback

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

Clear Lines Blog | Reply

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

Fane

When I press the button the debugger stops and makes yellow the line:
        Globals.ThisAddIn.TaskPane.Visible = true;
and the error "NullReferenceException was unhandled by user code"
                           'Object reference not set to an instance of an object'
I changed the line from private in 'internal CustomTaskPane TaskPane'
What I did wrong?

Fane Romania | Reply

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

Fane

On the code line: 'private CustomTaskPane taskPane;'
the word 'taskPane' is green underlined and the next message is displayed when the mouse is on it:
  "Field ExcelAddin.ThisAddin_C.TaskPane ' is never assigned to, and will always have its default value null".
This line of code is inside thisAddin class like this:
namespace ExcelAddIn_C
{
    public partial class ThisAddIn
    {
        private CustomTaskPane taskPane;

        internal CustomTaskPane TaskPane
        {
            get
            {
                return this.taskPane;
            }
        }
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            var messageBox = MessageBox.Show("Salutari din Comm Addin (C#)!");
            var taskPaneView = new TaskPaneView();
            var myTaskPane = this.CustomTaskPanes.Add(taskPaneView, "Fane Duru");
            myTaskPane.Visible = true;
        }

Fane Romania | Reply

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

mathias

From the code you attached, it seems that the following is happening: in ThisAddIn_Startup, a TaskPane is created as a local variable myTaskPane, but that variable is never assigned to the field taskPane. Adding the line this.taskPane = myTaskPane at the end of the ThisAddIn_Startup should address that issue.
Mathias

mathias United States | Reply

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

Faisal

how can i give selection event on a ribbon tab? any one please help me...

Faisal Bangladesh | Reply

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

Stephen Wood

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.

Stephen Wood United States | Reply

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

Lipozene

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

Lipozene United States | Reply

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

Jim Parsells

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?

Jim Parsells United States | Reply

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

Mathias

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

Mathias United States | Reply

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

Bill Smith

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!

Bill Smith United States | Reply

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

Jim Parsells

    This is how I did it. It may well not be the best way, but in my very limited case, it works.
Add the following to ThisAddin:
Protected Overrides Function CreateRibbonObjects() As IRibbonExtension()
        If MyRibbon Is Nothing Then
            MyRibbon = New Ribbon1
            MyRibbon.TabTasks.Visible = False
        End If
        Return New IRibbonExtension() {MyRibbon}
    End Function
This simply creates MyRibbon and sets my added group's visibility to False.
Also in ThisAddin, add the following:
    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
                WBDict.Add(WB, WBOpening)
            End If
            CurrentWorkBook = WBOpening
            If AccDB Is Nothing Then AccDB = New DataAccess
            MyRibbon.TabTasks.Visible = True
            'TestFindVal()
        Else
            MyRibbon.TabTasks.Visible = False
        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.

Jim Parsells United States | Reply

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

Jim Parsells

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)
            MyRibbon.TabTasks.Visible = True
        Else
            Dim WBOpening As New AWorkbook(WB)
            If WBOpening.IsValid Then
                If Not WBDict.ContainsKey(WB) Then
                    WBDict.Add(WB, WBOpening)
                End If
                CurrentWorkBook = WBOpening
                If AccDB Is Nothing Then AccDB = New DataAccess
                MyRibbon.TabTasks.Visible = True
            Else
                MyRibbon.TabTasks.Visible = False
            End If
        End If
    End Sub

Jim Parsells United States | Reply

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

Bill Smith

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.  

Bill Smith United States | Reply

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

mehow

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

mehow United Kingdom | Reply

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

mehow

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)
        {
            if (Globals.ThisAddIn.TaskPane.Visible == false)
            {
                Globals.ThisAddIn.TaskPane.Visible = true;
                this.button1.Label = "Hide Task Panel";
            }
            else
            {
                Globals.ThisAddIn.TaskPane.Visible = false;
                this.button1.Label = "Show Task Panel";
            }
        }

mehow United Kingdom | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS