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:



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 (5)

  1. 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?

  2. sam says:

    "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

  3. Gabhan Berry says:

    Sam,

    The CTP is a COM technology. By that I mean they are exposed via COM interfaces. In theory, one can use any COM language to build a CTP. This list includes VB6, C++ and the .NET languages.

    VBA can consume COM components but cannot create them. To use CTPs, Excel requires that your addin supports a QueryInterface call for the ITaskPaneConsumer interface.

    CTPs do not use XML. The XML in this post is for customising the Ribbon. The Ribbon API is a seperate API.

  4. Mark Southern says:

    Great blog, but please can you post some downloadable code for this one 🙂

  5. hi gabhan

    great article and i have managed to achieve this no problem.  

    i am now trying to extract the creation of the custom task pane out of the addin and into my own custom class which i can simply reference in the Connect method of the addin.

    i pass a reference of the host application into my custom class and i also implement the ICustomTaskPaneConsumer interface there.  the problem that i think i now have is that nothing is invoking the CTPFactoryAvailable method via the IQueryInterface of the ICTPConsumer, and therefore my code does not execute and create a CTP.

    having read through your text several times i am stumbling on this to which i believe is the root of my problem…    "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. "

    the fact that Excel makes QueryInterface call would indicate that it does this in the background.  i have looked for any hidden code in the project to see how this is achieved and cannot find any.  is it possible you could elaborate on this for me please.  ie do i have to call the method myself, and how do i get a reference to the ICTPFactory object?  or is this simply not possible and the ICTPConsumer interface can only be implemented in the actual addin itself?

    thanks

    matt