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 the standard Excel task pane
• Creating an advanced Excel task pane

Creating an Excel COM add-in project

We’ll start by creating a new ADX 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.

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.

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.

Switch to the AddinModule designer surface and click on the ellipses (…) button next to 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

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:

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.

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.

Add 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

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.

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.

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

Excel add-in development in Visual Studio for beginners:

• Part 1: Application and base objects
• Part 2: Customizing the Excel User Interface: What is and isn't customizable
• Part 3: Customizing Excel ribbons and toolbars
• Part 4: Customizing Excel main menu, context menus, and Backstage view
• Part 6: Working with Excel workbooks and worksheets: VB.NET examples
• Part 7: Working with Excel cell values, formulas and formatting: C# samples
• Part 8: Working with Excel charts
• Part 9: Working with Excel tables and ranges
• Part 10: Importing data from SQL databases and other sources to Excel
• Part 11: Working with Excel pivot tables: VB.NET code examples