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.
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.
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:
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.
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.
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 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.
In the Personal Filter Functions add-in, AlexJ left the code unlocked, so you can see how it works.
There is code in the following modules, which you can see in the Project Explorer list, under the RightClickFilterFunctions workbook.:
When you open or close the add-in file, or install or uninstall it, code in ThisWorkbook module runs automatically.
There are 4 procedures in the ThisWorkbook module. These procedures run other macros that
To see the macros that are called by the ThisWorkbook procedures,
There are constants at the top of the module, and 3 macros:
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"
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
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
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
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
2. Erlandsen Data Consulting: CommandBar Tools page
Click here to download the zipped Right-click Filter Add-in folder, which contains 2 Excel files:
Last updated: October 7, 2020 4:20 PM