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.
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
10 Comments
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?
Hi Albert,
As far as I know it’s not possible to create a custom task pane in vba.
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?
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.
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
Hello MJV,
Yes, it is possible to develop custom task panes with Visual Studio Community Edition.
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.
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!
Can you also show us the code to show/hide taskpanes for Microsoft Word and PowerPoint application?
Thanks
Hello Sri,
If you talk about Custom Task Panes, you can use the approach above: {an ADXTaskPane object}.Visible = {true or false}.