Handling Sort and Filter Events in Excel (Navneet Gupta)

  • Comments 6

Recently I helped two customers that were trying to solve this problem. They wanted to get notifications whenever Excel sorted or filtered data. This is not as easy as it sounds because Excel does not raise any events for sorting and filtering.

I found two possible solutions, one of which requires much less coding but does not cover every possible scenario and another which requires a bit more code but covers all the scenarios we could think of. I thought I’d share my findings with you here in this post.

Waiting for the Calculate Event

Any changes in data/formulas in Excel sheet results in a Sheet_Change event immediately followed by Sheet_Calculate event. Sort and Filter raises just the Sheet_Calculate event. This means that all you need to do is handle this event and write a bit of code.

So this would seem to solve our problem; wait for Sheet_Calculate event and see if it follows any Sheet_Change event and we are done. I was super excited after knowing this and thought that I found the solution. But I soon realized that if the user has turned on Manual Calculation mode then the Sheet_Calculate event is not fired until the user calculates manually. Also there isn’t a way to differentiate between Sort and Filter. In order to do that, we need to write a bit more code.

Repurposing Commands

What does this mean and how is it going to solve our problem?

Almost every action in Excel (or any other Office App) that the user can perform via the UI is a command. For example, saving a file is the FileSave command in the Excel object model. We can program Office applications to call a function we write for a given command. That way we can execute our custom code and then continue with the default behaviour.

There are many useful articles and blogs on MSDN that talk about repurposing commands. References to the related articles are summarized below.

In this post I am focusing on repurposing commands in Office 2007 and Office 2010, but we can repurpose commands in Office 2003 as well, although the method will be slightly different.. If you are interested in how to repurpose commands in Office 2003 take a look at this post.

I am assuming here that you have a basic understanding of customizing the Office Ribbon using Visual Studio. The MSDN series below goes into great depth for customizing the Fluent Ribbons (although these were written for Office 2007, they equally apply to Office 2010).

· Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3)

· Customizing the 2007 Office Fluent Ribbon for Developers (Part 2 of 3)

· Customizing the 2007 Office Fluent Ribbon for Developers (Part 3 of 3)

To repurpose a command, we will create a CustomUI in ribbon.xml and specify the commands we want to repurpose, then we will write functions to handle the command.

Ingredients

We will need the Control ID and Control Type of the commands we want to repurpose.

Control IDs

We will need control IDs of the commands to write the custon UI. The Microsoft Office team has documented all the Control IDs for every Office application; they can be downloaded for Office 2007 and Office 2010. This article explains how you can find them from VBA.

For a given command the control IDs are same across various versions of Microsoft Office. There can be multiple UI elements that can perform the same action and usually there is a separate control ID for each UI element.

Control Types

We will need the control type for the control IDs as well. The control type is used to find out the signature of the function used to repurpose a particular command. Declaring our function with wrong signature will not trigger any exceptions, Office will just not call our function and continue silently. Table 4 this article explains the relationship between control types and function signatures. In this scenario we will be repurposing the button and toggleButton control types. Here are the function signatures for them.

button

onAction – repurposed

C#: void OnAction(IRibbonControl control, ref bool CancelDefault)

VBA: Sub OnAction(control As IRibbonControl, byRef CancelDefault)

C++: HRESULT OnAction([in] IRibbonControl *pControl, [in,out] VARIANT _BOOL *fCancelDefault)

Visual Basic: Sub OnAction(control As IRibbonControl, byRef CancelDefault)

toggleButton

onAction - repurposed

C#: void OnAction(IRibbonControl control, bool pressed, ref bool cancelDefault)

VBA: Sub OnAction(control As IRibbonControl, pressed As Boolean, byRef cancelDefault)

C++: HRESULT OnAction([in] IRibbonControl *pControl, [in] VARIANT_BOOL *pvarfPressed, [in,out] VARIANT _BOOL *fCancelDefault)

Visual Basic: Sub OnAction(control As IRibbonControl, pressed As Boolean, byRef CancelDefault)

Below are the Control IDs for sort and filter commands.

Control ID

Control Type

Filter

toggleButton

AdvancedFilterDialog

Button

FilterReapply

Button

SortDialog

button

SortAscendingExcel

button

SortDescendingExcel

button

The Recipe

1. Add a Ribbon(XML) Item to the Project by right clicking on the Project in Solution Explorer and selecting Add Item …

image

2. Double click the Ribbon1.xml in Solution Explorer to Open it.

ribbon1

3. Remove the <ribbon> element and all of its child elements from Ribbon1.xml

4. The CustomUI schema provides <commands> elements to specify commands in Office Fluent UI.

The command element needs two attributes idMso and onAction attribute. idMso is the Control ID and onAction is the function that needs to be called when this command executes.

Below is the sample xml for various Sort and Filter commands.

<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="Ribbon_Load">
  <commands>
    <!--Repurpose Sort Commands-->
    <command idMso="SortAscendingExcel" onAction="CustomSortAscendingExcel"/>
    <command idMso="SortDescendingExcel" onAction="CustomSortDescendingExcel"/>
    <command idMso="SortCustomExcel" onAction="CustomSortCustomExcel"/>
    <command idMso="SortDialog" onAction="CustomSortDialog"/>
    <!-- Repurpose Filter Commands -->
    <command idMso="Filter" onAction="CustomFilter"/>
    <command idMso="AdvancedFilterDialog" onAction="CustomAdvancedFilterDialog"/>
    <command idMso="FilterReaply" onAction="CustomFilterReaply"/>
  </commands>
</customUI>

5. The only thing remaining is to write the function implementation. Below is sample implementation of onAction handlers for Sort commands.

Implementing every onAction in a separate function helps a lot during debugging. You can move common code into a separate function though. If you set the cancel parameter of the called function to false, Excel will execute the default implementation of that command after calling your function. This way you can just hook into user actions to know that user has executed the command and let Excel do the actual work. You write these functions in the Ribbon1 code-behind file. For instance, in C# the code would be:

       public void CustomSortAscendingExcel(IRibbonControl control, ref bool cancel)
        {
            Microsoft.Office.Interop.Excel.Range rngSelection = null;
            rngSelection = Globals.ThisWorkbook.Application.Selection as Microsoft.Office.Interop.Excel.Range;
            if (null != rngSelection)
            {
               //TODO: Custom Logic here
            }
        }
 
        public void CustomSortDescendingExcel(IRibbonControl control, ref bool cancel)
        {
            Microsoft.Office.Interop.Excel.Range rngSelection = null;
            rngSelection = Globals.ThisWorkbook.Application.Selection as Microsoft.Office.Interop.Excel.Range;
            if (null != rngSelection)
            {
                //TODO: Custom Logic here
            }
        }     
 
        public void CustomSortCustomExcel(IRibbonControl control, ref bool cancel)
        {
            Microsoft.Office.Interop.Excel.Range rngSelection = null;
            rngSelection = Globals.ThisWorkbook.Application.Selection as Microsoft.Office.Interop.Excel.Range;
            if (null != rngSelection)
            {
                //Perform sort
                Globals.ThisWorkbook.Application.Dialogs[Microsoft.Office.Interop.Excel.XlBuiltInDialog.xlDialogSort].Show();
                rngSelection.Select();
            }
        }
 
        public void CustomSortDialog(IRibbonControl control, ref bool cancel)
        {
            Microsoft.Office.Interop.Excel.Range rngSelection = null;
            rngSelection = Globals.ThisWorkbook.Application.Selection as Microsoft.Office.Interop.Excel.Range;
            if (null != rngSelection)
            {
                //Perform sort
                Globals.ThisWorkbook.Application.Dialogs[Microsoft.Office.Interop.Excel.XlBuiltInDialog.xlDialogSort].Show();
                rngSelection.Select();
            }
        }

I hope that this post will help you in repurposing commands in Office., In the beginning repurposing looks very difficult to do but once you have everything required to repurpose a command it is a piece of cake. Also note that Excel will call your function whenever a command executes until your customization is loaded. Repurposing is not limited to Excel you can apply it to any Office application.

References

For more information see:

· Temporarily Repurpose Commands on the Office Fluent Ribbon

· Office 2007 Control IDs

· Office 2010 Control IDs

· Find Control IDs from VBA

· Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3)

· Customizing the 2007 Office Fluent Ribbon for Developers (Part 2 of 3)

· Customizing the 2007 Office Fluent Ribbon for Developers (Part 3 of 3)

Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post
  • Hi, great tutorials.  But how can I catch the event, e.g. filter event, so that the add-in would know the changes of the filter criteria the user have made?

  • We've hooked these four commands and they work well when directly invoking the sort command (e.g. from the ribbon). However, they do not seem to fire when sorting data via the sort commands within an auto-filter dropdown. Is there a way to catch these sort actions?

  • Hi Lee,

    Did you look at SortAscendingExcel and SortAscendingExcel commands as per the control id workbook www.microsoft.com/.../details.aspx , They should be invoked from Auto-Filter dropdown.

    Thanks,

    Navneet

  • Hi Navneet,

    Thanks very much for the response. Yes, we have hooked the SortAscendingExcel and SortDescendingExcel commands. We've tried hooking the ribbon commands and also the Office 2003 command bars. None of those hooks seem to catch the sort operation when it's initiated from within the auto-filter dropdown.

    Any other ideas?

    thanks,

    Lee

  • Hi Navneet,

    How could I implement the right mouse button on a Ribbonbutton. In Office 2007/2010 I trigger an action when I click the left mouse button on a Ribbon button. With the right mouse button I have a context/ Dropdown menu. How I use my own dropdown or popup menu on a specific Ribbon button when I click the right button on it?

    Thanks

    Babak

  • I beg to differ, but the so-called "Filter Event" does not exist. As the name "SheetCalculate" indicates it is just a calculation event. Only when the filtered data has some calculations, the event is raised.

    I tried a couple of very simple apps (an application-level Add-In and a document-level Customization) and the filter never generated that kind of event.

Page 1 of 1 (6 items)

Handling Sort and Filter Events in Excel (Navneet Gupta)