Customize Excel Context Menus

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).

cellmenu00c

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.

cellmenu01

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.

cellmenu02

This made the repetitive actions more efficient, but not enough. You need to:

  1. Right-click on a cell
  2. Find the Filter command, and point to it
  3. 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

cellmenu03

Then, select a Command Bar to see its controls

cellmenu04

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).

cellmenu05

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:

cellmenu06

And here is the Context menu for a pivot table:

cellmenu07

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.

cellmenu08

__________

Customize Excel Context Menus http://blog.contextures.com/