When you right-click in Excel, a pop-up menu appears, with a list of commands that you can use. The list changes, depending on where you’ve clicked, so it’s called a “Context Menu”.
But, even though those pop-up menus are helpful, they might not have all the commands that you like to use. Or, the commands might be there, but buried a few layers deep in the sub-menus.
Maybe you’d like to add a few commands, but there isn’t a built-in way to customize those menus, like there is for the Ribbon and Quick Access Toolbar (QAT).
AlexJ to the Rescue
Fortunately, there is good news! Last week, I heard from my friend, and fellow Canadian, AlexJ. He was tired of travelling up to the QAT, where his favourite commands were stored, so he created code that adds new items to the Context Menus, and sent me his sample workbook.
Thanks, AlexJ, for sharing your sample file, so we can all customize our Context Menus. I’ll let AlexJ tell you his story, while I add a few of my favourite commands to that code.
How It Happened
Alex J: A lot of my work lately involves reviewing tables and pivot tables with a team in meetings and MS Lync conferences. When we’re doing this, there is typically a lot of filtering of tables, pivot tables, or ranges to zoom in on groups of data.
I had been in the habit of using the controls at the top of the table to select or clear filters. Doing this repetitively became frustrating, though.
Use the Right-Click Filters
I noticed that the Right-Click popup menu (the Context menu) has a group for Filter, and I could use items like Filter By Selected Cell’s Value.
A little side benefit – if you use this command on a range which does not have filters applied, it applies filters automatically, as part of the Filter By Selected Cell’s Value operation.
This made the repetitive actions more efficient, but not enough. You need to:
- Right-click on a cell
- Find the Filter command, and point to it
- Find the Select by Selected Cell’s … that you need, and click on it.
I think it’s this extra cognitive effort that slows me down, not just the number of clicks involved.
Customize the Context Menu
To find a more efficient solution, I looked up a piece of code at Customizing Context Menus in All Versions of Microsoft Excel, written by Ron de Bruin. (I will not regurgitate the technique for adding items to the context menus using VBA or the Custom UI Editor – Ron explains it very effectively.)
Ron’s article references Ole P. Erlandsen’s add-in for Command Bar Tools to find the Context Menu Bar Names, control IDs and control image Face IDs.
What I learned is that, while Ron explains that the “Cell” context menu has 2 different versions:
- normal view
- page break view
there are 2 others that exist depending on whether the selected object is:
- a table or
- a pivot table
Ole’s addin tool was REALLY useful to figure this out. After installing, it appears on the Add-Ins tab of the Ribbon.
Select the command to List all CommandBar controls
Then, select a Command Bar to see its controls
From the list, I was able to identify the context menu for tables and for pivot tables, and the controls I needed in each of these (they all have slightly different specifications).
Sample Code For Filter Selection
Based on this information, I created code to add 2 new commands at the top of the right-click’s Context menu:
- Filter By Selected Cell’s Value
- Show All (or Clear Filter)
If you prefer, you could use one of the other Filter by options instead:
- Filter by Selected Cell’s Color
- Filter by Selected Cell’s Font Color
- Filter by Selected Cell’s Icon
I’ve installed the code in my Personal Macro Workbook (personal.xla or personal.xlsb), and added code in the ThisWorkbook module, to activate the function when the personal workbook is loaded (this is commented out in the sample file).
As a result, I now have context menus that allow for filter selection (and clearing) with only one extra click after right click.
Here is the Context menu for a named table:
And here is the Context menu for a pivot table:
Download the Sample File
To see AlexJ’s code, and copy it to your own file, please visit the AlexJ’s Sample Files page on my Contextures website.
In the VBA section, look for VB0002 – Customize Context Menus.
Copy the code from both the ThisWorkbook module, and the mCustomPopup module.
__________
