Pieter van der Westhuizen

Creating custom task panes for Excel 2013 – 2003

When building task panes for Microsoft Excel, Add-in Express provides developers with two options; using the standard Microsoft Office task pane or the more flexible Add-in Express advanced Office task panes.

Let’s have a look at how you can use both of these approaches in your Microsoft Excel add-ins with Add-in Express for Office and .net.

Creating an Excel COM add-in project

We’ll start by creating a new ADX COM Add-in project in Visual Studio.

Creating an Excel COM add-in project in Visual Studio

Next, select your programming language of choice (C#, VB.NET or C++.NET) and the minimum version of Office that will be supported by your Excel add-in.

Select your programming language and the minimum Office version to be supported by your Excel add-in

Creating the standard Excel task pane

Standard Excel task panes are what you are used to seeing in Microsoft Excel. Standard task panes cannot be minimized, only closed and you can drag and drop it to one of four locations (left, right, top, and bottom) inside the Excel main application window. Standard task panes only works in Office versions 2007 and up.
Before we can create a standard Excel task pane, we first need to add a standard Winforms User Control, by selecting Add User Control… from the Visual Studio Project menu and selecting the User Control item template from the Add New Item dialog.

Selecting the User Control item template from the Add New Item dialog

After you’ve added the control, design the UI of your Excel task pane as you would any other Windows form. In this example we’re building a simple random data generator.

Design the UI of your Excel task pane

Switch to the AddinModule designer surface and click on the ellipses (…) button next to the TaskPanes property.

The TaskPanes property

In the ADXTaskPane Collection Editor dialog, click on the Add button and set the following properties of the new item:

  • Name: randomDataTaskPane
  • ControlProgID: ExcelTaskPane.ctlPane
  • DockPositionRestrict: ctpDockPositionRestrictNoHorizontal
  • SupportedApps: Excel
  • Title: Random Data Pane

ADXTaskPane Collection Editor

By setting the DockPositionRestrict property to ctpDockPositionRestrictNoHorizontal, we only allow the user to dock the custom task pane to either the left or the right of the Excel main window.
At this stage, we’re ready to build, register and run our Excel addin. When Excel starts you should see your custom task pane docked on the right hand side, similar to the following image:

The newly created standard Office task pane in Excel 2013

Controlling the visibility of a standard Excel task pane

The standard Office task panes cannot be minimized, only closed. But how do you allow the user to see the task pane again after they’ve closed it? First, let’s add a new ADXRibbonTab control to the AddinModule designer surface and add a Ribbon group and button to it.

Controlling the visibility of a standard Excel task pane

Select the ribbon button and double-click next to its OnClick event to generate an event handler stub. Add the following code to the OnClick event- it will show the task pane if it is not visible and hide it when it is:

private void showHideTaskPaneRibbonButton_OnClick(object sender, IRibbonControl control, bool pressed)
{
    randomDataTaskPane.Visible = !randomDataTaskPane.Visible;
}

Creating an advanced Excel task pane

The Add-in Express advanced Excel task pane improves on the standard Office task pane by allowing you to specify that the task pane can be minimized as well as being able to host multiple task panes per dock location. Some of the key benefits of using an Add-in Express advanced task pane instead of a standard Excel task pane are:

  • Support for all version of Microsoft Excel from 2000 up to Excel 2013;
  • Advanced Excel task panes can be highlighted programmatically to attract the users’ attention to the task pane;
  • Advanced task panes can be dragged and dropped between for dock locations or fixed in one docking location; and
  • Can be minimized or hidden.

Start by adding an ADXExcelTaskPaneManager component to the AddinModule designer surface.

Adding an ADXExcelTaskPaneManager component to the AddinModule designer surface

Add a new ADX Excel Task Pane to the COM add-in project:

Adding a new ADX Excel Task Pane to the COM add-in project

Select the ADXExcelTaskPanesManager we’ve added earlier and add a new item to its Items collection. Set the following properties on the newly added item:

  • AllowedDropPositions: Left;Right
  • AlwaysShowHeader: True
  • CloseButton: True
  • IsDragDropAllowed: True
  • Position: Left
  • TaskPaneClassName: ExcelTaskPane.ADXExcelTaskPane

Setting the properties on the advanced Excel task pane

Controlling an advanced Excel task pane’s visibility

Lastly, let’s see how you can show and hide the advanced Excel task pane programmatically. Switch to the AddinModule designer surface and select the Ribbon Tab we’ve added earlier. Add another ribbon group and button to the tab.

Showing and hiding the advanced Excel task pane programmatically

As with the previous button, you can double-click next to the OnClick event in the buttons’ event list to generate an event handler stub. Add the following code to the OnClick event to hide or show the advanced Excel task pane:

private void showhideAdvancedPaneRibbonButton_OnClick(object sender, IRibbonControl control, bool pressed)
{
    if (!adxExcelTaskPanesCollectionItem1.TaskPaneInstance.Visible)
    {
        adxExcelTaskPanesCollectionItem1.ShowTaskPane();
    }
    else
    {
        adxExcelTaskPanesCollectionItem1.TaskPaneInstance.Hide();
    }
}

When running your Excel add-in you should now see the custom task pane as well as the ribbon tab in Excel.

The advanced task pane and the ribbon tab in Excel 2013

Thank you for reading. Until next time, keep coding!

Excel add-in development in Visual Studio for beginners:

10 Comments

  • https://secure.gravatar.com/avatar/120bd9c974ca8ff0cfaadb86663baee5?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G albert park says:

    I’m looking through,
    The way to make custom task pane in vba.
    Is that impossible?
    And only way to make it is via C# or VB?

  • https://secure.gravatar.com/avatar/e1a4c2b21a5186e0b27c1c601f418b76?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Pieter van der Westhuizen says:

    Hi Albert,

    As far as I know it’s not possible to create a custom task pane in vba.

  • https://secure.gravatar.com/avatar/90e8b1e310fc33ac406a65a067299f88?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Rudy says:

    Hi,

    I have three ribbon buttons and three task panes.
    Instead of hiding a pane I would like to bring it to the front. But .BringToFront doesn’t work.
    How can I solve this problem?

  • https://secure.gravatar.com/avatar/ab4ec2858cfdf1e44dadf8c50fae314d?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Dmitry Kostochko (Add-in Express Team) says:

    Hi Rudy,

    You can use the Activate() method. Also, you can use the Show() method before Activate to make sure your task pane is shown.

  • https://secure.gravatar.com/avatar/2ed83b25f764813604df85d620dec1b5?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G MJV says:

    Hi Pieter,

    Great info! This is exactly the sort of thing I am looking for. Just wondering, is it possible to develop custom task panes using the community edition of visual studio? Or do you need to purchase the professional edition?\

    Cheers

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello MJV,

    Yes, it is possible to develop custom task panes with Visual Studio Community Edition.

  • https://secure.gravatar.com/avatar/2124ebf9edbc629b2038896922ba5385?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G joker_guy says:

    Thank you so much ! I have seen “Insertion Range” on your pane, which allow the user to select the range if they want. How did you make that? Directly from toolbox or somewhere? If it’s from toolbox, which one is that? I am in urgent need of that now.
    Look forward for your reply.
    Thank you.

  • https://secure.gravatar.com/avatar/e1a4c2b21a5186e0b27c1c601f418b76?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Pieter van der Westhuizen says:

    Hi There,

    The insertion range is a simple textbox and a button. Unfortunately there are no insertion control, you would have to build it yourself.

    Good luck and thank you for the comment!

  • https://secure.gravatar.com/avatar/9f4a094572e81e842106f860e3da1b9f?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Sri says:

    Can you also show us the code to show/hide taskpanes for Microsoft Word and PowerPoint application?

    Thanks

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Sri,

    If you talk about Custom Task Panes, you can use the approach above: {an ADXTaskPane object}.Visible = {true or false}.

Post a comment

Have any questions? Ask us right now!