Develop Excel task pane and action panes for
Excel 2019 - 2003 addin in VB.NET, C#

Add-in Express™
for Microsoft® Office and .net

Add-in Express Home > Add-in Express for Office and .NET > Online Guide > Add-in Express components > Advanced Excel task panes

Advanced Excel task panes

Add-in Express empowers Microsoft Excel COM add-in developers with version-neutral advanced task pane for Excel 2000, 2002 (XP), 2003, 2007, 2010, 2013, 2016 and Excel 2019.

Please see The UI mechanics above for the detailed description of how Add-in Express panes work.

Below you see the list containing some generic terms mentioned in An absolute must-know and their Excel-specific equivalents:

• <Manager> - AddinExpress.XL.ADXExcelTaskPanesManager, the Excel Task Panes Manager
• <Item> - AddinExpress.XL.ADXExcelTaskPanesCollectionItem
• <Form> - AddinExpress.XL.ADXExcelTaskPane

Application-specific features

ADXExcelTaskPane provides 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 combination to Excel or to the pane. Sending the key combination to the pane 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 messages in the ADXPostMessageReceived event.

Building an advanced Excel task pane

The sample Excel task pane project below is written in VB.NET, but you can also write your projects in C# and C++ in the same way. In the very first step you create an Excel add-in project as shown in a sample Office COM add-in. Now, let's create an advanced task pane for Excel 2000 - 2019.

1. Adding the Excel Task Panes manager

Add the Excel Task Panes Manager to the module. You can do this by choosing the corresponding component shown on the add-in module toolbar. Or, you can right-click the design surface of the add-in module and choose the corresponding command in the context menu.

2. Adding an advanced Excel task pane

To add a 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. 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. Specifying context for your Excel task pane

Add a new item to the Items collection of the Excel Task Panes Manager:

Now select the item just added and set its properties as follows:

That is, your task pane will be shown in the Right position and the Close button will be shown in the pane header.

5. 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 handling events that the Excel Events component provides:

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 

6. Running the add-in

Finally, you rebuild the add-in project, register it, run Excel, and find your custom task pane in place.