Develop custom Excel task panes and action panes for
Microsoft Excel 2007 - 2000 in .NET: VB, C#, C++

Add-in Express Home > Add-in Express.NET > Online Guide > Creating custom Excel task panes

Advanced Excel Task Panes

The only Excel version that allows adding custom task panes is Excel 2007. To be more precise, VSTO 2005 (not SE!) as well as VSTO 2008 allow customizing Excel 2003 workbooks and templates with action panes, not custom task panes. Add-in Express provides Microsoft Excel COM add-in developers with version-neutral custom panes for Excel 2000, 2002 (XP), 2003 and Excel 2007. To achieve this, Add-in Express provides ADXExcelTaskPane – the base class for every .NET form to be embedded into Excel windows. This class provides several Excel-specific properties and events used to access Excel objects from the task pane form.

The Excel Task Pane Manager component (ADXExcelTaskPanesManager), which orchestrates custom task panes, is the second part of the technology. This component provides a collection of items; each item of the collection connects the pane to the context and visualization settings. The technology relies heavily on the Win32 API in order to conform to Excel inner window chains, structures, and messages.

You use these elements in the following order:

• For your add-in project, open the Add New Item dialog, and choose the Add-in Express Excel Task Pane item. This adds a task pane, of the ADXExcelTaskPane type, to your project.
• Right-click the designer surface of your add-in module and choose the Add Excel Task Panes Manager command in the context menu (see also Commands of the Add-in module). 
• Add an item to the collection provided by the manager component and, in the item's properties, specify the task pane (see the TaskPaneClassName property) and its position in the Excel window (see the Position property).

When the manager detects a context change in the host application, it searches the collection for enabled items and raises the ADXBeforeTaskPaneInstanceCreate event. This event is cancellable; it provides context-sensitivity for your custom Excel task panes. If the event isn't cancelled, the manager creates an instance of the pane specified in the TaskPaneClassName property of the item and shows that instance in the corresponding window region. While being manipulated in this way, the task pane raises the ADXBeforeTaskPaneShow and ADXAfterTaskPaneShow events. The ADXBeforeTaskPaneInstanceCreate and ADXBeforeTaskPaneShow events allow you to control how the form creates and shows.

DXExcelTaskPane, an Add-in Express task pane, also provides highly useful events unavailable in the Excel object model: ADXBeforeCellEdit and ADXAfterCellEdit.

Keyboard and focus

ADXExcelTaskPane provides an event that may be useful for your Excel add-in. The event is called ADXKeyFilter. It deals with a feature of Excel that captures the focus if a key combination which can be processed by Excel is pressed. By default, Add-in Express panes do not pass key combinations to Excel. In this way, we are assured that the focus will never leave the pane unexpectedly.

To illustrate the feature, imagine that you need to let the user press Ctrl+S and get the workbook saved while your custom pane is focused. In such a scenario, you have two ways: 

• You process the key combination in the code of the pane and use the Excel object model to save the workbook. 
• Or, you send this key combination to Excel using the ADXKeyFilter event.

Besides the obvious difference between the ways, the former leaves the focus on your custom Excel task pane while the latter effectively moves it to Excel because of the focus-capturing feature just mentioned.

The algorithm of key processing is as follows. Whenever a single key is pressed, it is sent to the pane. When a key combination is pressed, ADXExcelTaskPane determines if the combination is a shortcut on the pane. If it is, the key press is sent to the pane. If it isn't, ADXKeyFilter is fired and the key combination is passed to the event handler. Then the event handler specifies whether to send the key press to Excel or to the pane. The latter is the default behavior. Note that sending the key combination to Excel will result in moving the focus off the pane. The above-said implies that the ADXKeyFilter event never fires for shortcuts on the pane's controls.

Also, ADXKeyFilter is never fired for hot keys (Alt + an alphanumeric symbol). If ADXExcelTaskPane determines that the pane cannot process the hot key, it sends the hot key to Excel, which activates its main menu. After the user has navigated through the menu by pressing arrow buttons, Esc, and other hot keys, opened and closed Excel dialogs, ADXExcelTaskPane will get focus again.

Wait-a-little and focus again

The Advanced Exel Task Pane provides a simple infrastructure that allows implementing the wait-a-little schema: the ADXPostMessage method and the ADXPostMessageReceived event.

Currently we know at least one situation that this trick is required. Imagine that you show a custom task pane and you need to set the focus on a control on this pane. It isn't a problem to do this in, say the Activated event. Nevertheless, it is useless because Excel, continuing its initialization, captures the focus off the pane. With the above-said method and event you can make your custom Excel task pane look like it never loses focus: in the Activated event handler, you call the ADXPostMessage method specifying a unique parameter set and, in the ADXPostMessageReceived event, you check for that parameter set; when you get the appropriate ADXPostMessageReceived event, you set the focus on the control. Here we are! Beware, there will be a huge lot of inappropriate ADXPostMessageReceived events.

 Add-in Express provides custom Excel task panes for COM add-ins that support Microsoft Excel 2000, 2002 (XP), 2003 and Excel 2007 and offers some more features for creating advanced GUI of your Excel add-in.

Building an Excel custom task pane

The sample Excel task pane project below is written in VB.NET, but you can also write your projects in C#, C++ and RO Chrome in the same way. The first three steps of creating this sample task pane for Excel are the same as the first three steps described in a sample Office COM add-in. Please perform them first.

1. Adding the Excel Task Panes manager

Now you use the corresponding command in the context menu of the add-in module in order to add the Excel Task Panes Manager to the module.

2. Adding a custom task pane

To add a custom task pane to your Excel add-in project, choose the corresponding wizard in the Add New Item Dialog.

3.Customizing the Excel pane

On your pane, you can use any .NET controls such as calendars, edit boxes, grids, list views, etc. In this example, we added a label to show the address of the selected Excel range.

Add the following method to the task pane code:

 Public Sub RefreshMe(ByVal Range As Excel.Range)
 'relative address
   Dim Address As String = Range.AddressLocal(False, False)
  Me.AddressLabel.Text = Address
 End Sub

4. Accessing Excel objects

Add Excel Events component to the add-in module: right-click the module, choose the Add Events item in the context menu, and then choose Excel events in the list.

Now you can write the following code:

 Private Sub adxExcelEvents_SheetSelectionChange( _
      ByVal sender As System.Object, _
      ByVal sheet As System.Object,
      ByVal range As System.Object) _
         Handles adxExcelEvents.SheetSelectionChange
      RefreshPane()
   End Sub

   Private Sub adxExcelEvents_WorkbookActivate( _
      ByVal sender As System.Object, _
      ByVal hostObj As System.Object) _
         Handles adxExcelEvents.WorkbookActivate
      RefreshPane()
   End Sub

   Private Sub adxExcelEvents_SheetActivate( _
      ByVal sender As System.Object, _
      ByVal hostObj As System.Object) _
         Handles adxExcelEvents.SheetActivate
      RefreshPane()
   End Sub

   Private Sub RefreshPane()
      Dim selection As Excel.Range = _
         CType(Me.ExcelApp.Selection, Excel.Range)
      Dim instance As ADXExcelTaskPane1 = _
         CType(AdxExcelTaskPanesCollectionItem1.TaskPaneInstance, _
         ADXExcelTaskPane1)
      If instance IsNot Nothing Then
         If Not instance.Visible Then instance.Show()
         instance.RefreshMe(selection)
      End If
      Marshal.ReleaseComObject(selection)
   End Sub

5. Specifying context for your custom Excel task pane

Add a new item to the Items collection of the Excel Task Panes Manager, select your task pane class in the TaskPaneClassName property, and specify context as follows: 

6. Running the add-in

Finally, you rebuild the add-in project, run Excel, and find your custom pane embedded.

Also, please see some more advanced features provided by Add-in Express for creating advanced Excel GUI.

Back to Add-in Express.NET homepage