Excel Filter Right-Click Macros

When you right-click on an Excel worksheet, a popup menu appears, with commands related to the active cell. If the cell is in a pivot table, named Excel table or a range with filters, the menu has a Filter command. The macros shown here, from AlexJ, put key Filter commands at the top of the popup menu, where they're much easier to use.

Introduction

This animated screen shot shows how the filter right-click macros work. Right-click on a cell, and the relavent filter commands appear at the top of the pop-up menu. There are written steps in the sections below.

The first right-click is on a named Excel table cell, and the second right-click is on a pivot table row label cell.

Install the Filter Add-in

The filter right-click macros are stored in an Excel add-ins file (xlam). Before you can use the add-in, follow the steps on the Install and Use Excel Add-ins page.

The instructions on that page tell you how to:

• Store the xlam file in your Addins folder, or another trusted location.
• In Windows Explorer, unblock the file, if necessary
• Then, open Excel, and activate the add-in, by opening the Excel Add-ins dialog box. Look for the Add-in named Personal Filter Functions.

Use the Filter Add-in

After you install and enable the Personal Filter Functions add-in, it's ready to use.

Just for comparison, here's the right-click menu for a pivot table Row label cell, before installing the Personal Filter Functions add-in.

• The top 2 commands are Copy and Format Cells.

After Installing the Add-in

After you install the Right-Click Filter add-in, the right-click menu looks different.

Here's the right-click menu for a pivot table Row label cell, after installing the Personal Filter Functions add-in.

• There are 2 filter commands at the top of the right-click menu now
• The top command is "Keep Only Selected Items". That's a pivot table label filter, which lets you quickly filter the results for one or more items.
• The other Filter command, "Clear Filter From Category2" is dimmed out, because no filter has been applied to the Category2 field.

Different Filter Commands

The filter commands at the top of the right-click menu will change slightly, based on which type of cell you right-click.

In the screen shot below, a cell in worksheet list is active.

• The first command lets you filter that list be the value in the selected cell
• The Show All and Reapply commands are dimmed out, because they can't be used in the current situation.

The filter commands are the same if you right-click a data cell in a named Excel table.

After a filter is applied, the Show All and Reapply commands are available.

How the Filter Add-in Works

In the Personal Filter Functions add-in, AlexJ left the code unlocked, so you can see how it works.

• In Excel, press Alt + F11 to open the Visual Basic Editor
• At the left, in the Project Explorer list, look for RightClickFilterFunctions (Personal Filter Functions)

There is code in the following modules, which you can see in the Project Explorer list, under the RightClickFilterFunctions workbook.:

• Microsoft Excel Objests Folder
  • ThisWorkbook module
• Modules Folder
  • mMenuCustomPopup module

ThisWorkbook Code

When you open or close the add-in file, or install or uninstall it, code in ThisWorkbook module runs automatically.

• In the Microsoft Excel Objects folder, double-click on ThisWorkbook
• The code appears in the code window, at the right.

There are 4 procedures in the ThisWorkbook module. These procedures run other macros that

• add the Personal Filter Function commands to the right-click menu
• or to delete the Personal Filter Function commands.

mMenuCustomPopup Code

To see the macros that are called by the ThisWorkbook procedures,

• In the Modules folder, double-click the mMenuCustomPopup module
• The module's code appears in the code window, at the right.

There are constants at the top of the module, and 3 macros:

1. Delete the Commands

2. Delete Commands Macro

3. Add the Commands

Constants

At the top of the module, there are 4 contstants, which are used in the macros in that module.

By using a unique tag for the Filter commands, "Filter_Control_Tag", AlexJ is able to identify them later, and use a macro to delete them.

Const MenuName1 As String = _
    "List Range Popup"
Const MenuName2 As String = _
    "PivotTable Context Menu"
Const MenuName3 As String = "Cell"
Const myTag As String = _
    "Filter_Control_Tag"

Delete the Commands

This macro deletes the commands from the pop-up menus. It runs when you close the add-in file, or uninstall it.

This macro calls another macro 3 times -- once for each of the right-click menus where the filter commands will be added.

Sub Delete_CustomPopupControls()
  Call DeleteFromCommandBar(MenuName1)
  Call DeleteFromCommandBar(MenuName2)
  Call DeleteFromCommandBar(MenuName3)      
End Sub

Delete Commands Macro

Here is the code that runs 3 times to delete the commands on the 3 menus.

Sub DeleteFromCommandBar _
    (nmContextMenu As String)
  Dim ContextMenu As CommandBar
  Dim ctrl As CommandBarControl

  ' Set ContextMenu to Cell context menu
  Set ContextMenu = _
    Application.CommandBars(nmContextMenu)

  ' Delete the custom controls with
  ' the Tag : My_Cell_Control_Tag.
  For Each ctrl In ContextMenu.Controls
      If ctrl.Tag = myTag Then
          ctrl.Delete
      End If
  Next ctrl

End Sub

Add the Commands

The final macro in this module adds the custom Filter commands to each of the 3 right-click menus.

Before it adds the commands though, it calls the macro to delete them. That's a safety step, to make sure that the commands aren't duplicated on the right-click menus.

Sub Initiate_Custom_Popup()
  Dim ContextMenu As CommandBar
  ' Delete controls first to avoid duplicates
  Call DeleteFromCommandBar(MenuName1)
  Call DeleteFromCommandBar(MenuName2)
  Call DeleteFromCommandBar(MenuName3)
  
  Set ContextMenu = _
    Application.CommandBars(MenuName1)
  '       "List Range Popup"
  With ContextMenu.Controls
    .Add(Type:=msoControlButton, _
      ID:=12231, before:=1).Tag = myTag
    '       &ReApply Filter
    .Add(Type:=msoControlButton, _
     ID:=900, before:=1).Tag = myTag
    '       &Clear Filter
    .Add(Type:=msoControlButton, _
     ID:=12232, before:=1).Tag = myTag
    '   Filter by Selected Cell's &Value
  End With
  
  Set ContextMenu = _
    Application.CommandBars(MenuName2)
  '       "Pivot table Context Menu"
  With ContextMenu.Controls
    .Add(Type:=msoControlButton, _
     ID:=605, before:=1).Tag = myTag
    '       &Clear Filter
    .Add(Type:=msoControlButton, _
     ID:=12523, before:=1).Tag = myTag
    '       &Keep Only Selected Items
  End With
  
  Set ContextMenu = _
    Application.CommandBars(MenuName3)
  '       "CELL Context Menu"
  With ContextMenu.Controls
    .Add(Type:=msoControlButton, _
     ID:=12231, before:=1).Tag = myTag
    '       &ReApply Filter
    .Add(Type:=msoControlButton, _
     ID:=900, before:=1).Tag = myTag
    '       &Clear Filter
    .Add(Type:=msoControlButton, _
     ID:=12232, before:=1).Tag = myTag
    '       &Keep Only Selected Items
          
  End With
  
  Set ContextMenu = Nothing
End Sub

Programming References

AlexJ used a couple of reference sites while he worked on the Right-Click Filter Functions add-in.

1. Microsoft Site: Customizing Context Menus in All Versions of Microsoft Excel

• Instructions and sample code

2. Erlandsen Data Consulting: CommandBar Tools page

• Download the add-in from this page, to create a list of all available CommandBars in Excel, with a detailed list of all controls (menu items, buttons) on each CommandBar.

Download the Sample File

Click here to download the zipped Right-click Filter Add-in folder, which contains 2 Excel files:

1. Personal Filter Functions.xlam --This is the add-in file in xlam format (macro-enabled add-in). Follow the steps above, to install the Filter Add-in.
2. filtersampledata_aj.xlsx - This workbook has sample data, for testing the add-in. This file has a pivot table, a named Excel table, and a worksheet filtered list.

More Tutorials

AutoFilter Macros

List AutoFilter Macros

Install Excel Add-ins

AutoFilter Basics

AutoFilter Tips