Custom Task Panes

A Custom Task Pane (CTP) is a user interface component in Office which is used to provide a non-modal window. Some of Excel's features, such as the PivotTable field list and Reseach tool, use CTPs.  

CTPs are exposed to customer extensions via the Office extensibility COM libraries. Any Excel developer can develop their own CTP and have it work just like the built-in CTPs.

Despite what some people think, you do not need VSTO to build a CTP.

In this post I will develop an Excel feature called: annotations. Annotations (as implemented here) are basically free-from textual comments that can be added to a workbook. They are added into the file but are not displayed on the grid. You can use them to store notes or commentary in the workbook. They are displayed and edited in a Custom Task Pane as shown below.

The annotations are stored within the CustomXMLParts collection of the workbook. CustomXMLParts are a new feature in Excel 2007 which enable Excel developers to store their own XML within a workbook. I will cover CustomXMLParts in my next post.

What are Custom Task Panes?

A CTP is a simple window that contains an ActiveX control. Excel manages the CTP window (creates it, destroys it, handles its window messages etc.) and the ActiveX control provides all the features. The CTP is really just a container, but a container that is nicely integrated into Excel. A CTP can be docked inside the main Excel window or can float. The user can resize it, move it and close it and all of this is handled for us by Excel.

Building a Custom Task Pane

Creating a CTP itself is very simple. CTP functionality is provided via a COM interface called ICustomTaskPaneConsumer. From a .NET point of view, this interface is implemented in Microsoft.Office.Core. When Excel loads our COM addin it performs a QueryInterface call to see whether or not our addin implements the ICustomTaskPaneConsumer interface. If it does, Excel calls the ICustomTaskPaneConsumer.CTPFactoryAvailable method. It is in this method that our addin creates the CTP.

Let's take a look at the code.

public class Connect : Object, Extensibility.IDTExtensibility2, IRibbonExtensibility, ICustomTaskPaneConsumer {

  public void CTPFactoryAvailable(ICTPFactory CTPFactoryInst) {

    _ctpFactory = CTPFactoryInst;

     CustomTaskPane ctp = _ctpFactory.CreateCTP("ExcelExtensions.AnnotationsCtrl", "Annotations", Missing.Value);

_annotationsExt = new AnnotationsExtension(ctp, _application);

    

  }

}

The Connect class implements the ICustomTaskPaneConsumer interface. Its implementation of the CTPFactoryAvailable method does two things. Firstly, it stores the ICTPFactory object passed to it by Excel and secondly it creates a CTP using the ICTPFactory.CreateCTP method. The first parameter to the CreateCTP method is the ProgId of the ActiveX control that the CTP is to host. The CTP will create an instance of this ActiveX control and place it within the CTP window.  

CreateCTP returns a CustomTaskPane object which we store for later use. The CustomTaskPane object is how our addin can interact with the CTP window itself. In the example above, we store the CustomTaskPane object inside an  AnnotationsExtension object but we can actually store it anywhere we like. The AnnotationsExtension object is a class inside our addin - the details of this class will be covered in the next post - for now, all we need to know is that it is a conceptual wrapper of our CTP.

So, the basic workflow in creating a Custom Task Pane is as follows:

Displaying a Custom Task Pane

So far we have created a CTP. We have a reference to it and the CTP knows which ActiveX control it is hosting. However, we need to explcitly display the CTP in order for it to be visible.

We do this via the CustomTaskPane.Visible property. Setting this property to true or false will display or hide the CTP (notice that the CTP is hidden - not destroyed).

This makes sense because we'd typically want to show or hide our CTP based on some user action, like a user clicking a button on the Ribbon. In this example we'll add an Annotations button onto the Review tab of the Ribbon.

<tab idMso="TabReview">

  <group id="AnnotationsGroup" label="Notes" insertBeforeMso="GroupComments">

    <toggleButton id="AnnotationsButton" onAction="OnAnnotationsClicked" label="Annotations"

      imageMso="ExchangeFolder" supertip="Add new and view existing annotations." size="large"/>

  </group>

</tab>

We then implement the OnAnnotationsClicked event handler in our COM addin.

public void OnAnnotationsClicked(IRibbonControl Control, bool IsPressed) {

_annotationsExt.Visible = IsPressed;

}

Because we added a toggleButton control to the Ribbon, our event handler gets passed a boolean which indicates whether the toggleButton is pressed or not. All we need to do is set the Visible property of the CTP equal to this value.

Writing the ActiveX Control

We now have enough code to create and display a CTP when a button on the Ribbon is pressed. The final bit (and the harder bit) is writing the ActiveX control ... this is the thing that actually implements our custom features.

In this example, our ActiveX control reads and write annotations to the CustomXMLParts collection of the active workbook. I'll be covering the CustomXMLParts collection, how it is stored in the new Office XML file formats and how the ActiveX control works next time.

What's great is that the CTP can host any ActiveX control. So even though the CTP itself is native code, we can write our ActiveX control in .NET code and it all works.

Summary

To create a Custom Task Pane using managed code:

• Add a reference to Microsoft.Office.Core;
• Implement the ICustomTaskPaneConsumer interface on your addin class (i.e., the Connect class);
• In your implementation of ICustomTaskPaneConsumer.CTPFactoryAvailable call CreateCTP on the supplied ICTPFactory object;
• In the call to CreateCTP, specify the ProgId of the ActiveX control you want to host in the CTP;
• Store the CustomTaskPane object returned from CreateCTP and toggle its Visible property to show and hide the CTP as and when required; 

Links

The following link gives more information on creating Custom Task Panes:

• https://msdn.microsoft.com/en-us/library/aa942864.aspx

Next Time ...

We'll take a look at how the Annotations ActiveX control uses the CustomXMLParts collection to store the annotations entered by the user.

Comments

• Anonymous
  August 15, 2008
  I've got a few features that could make use of CTP's, so I'm looking forward to more on this. What is the oldest version of Excel that supports them?

• Anonymous
  August 15, 2008
  "Despite what some people think, you do not need VSTO to build a CTP" Are you saying that it is possible to create a CTP in Excel 2007 by using XML + VBA alone and without depending on any VS language If this is the case then this is great news and I will be looking forward to the next post Else this is just a post for Promoting VS Sam

• Anonymous
  August 20, 2008
  The comment has been removed

• Anonymous
  September 10, 2008
  Great blog, but please can you post some downloadable code for this one :-)

• Anonymous
  August 23, 2011
  The comment has been removed