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/

You may also like...

4 Responses

  1. Doug Glancy says:

    AlexJ, you may be interested in my MenuRighter utility. It lets you add any one of thousands of Excel 2003 controls to any context menu bar. To read more you don’t even have to leave this site; Debra was kind enough to post about it three years ago now: http://blog.contextures.com/archives/2011/12/02/customize-excel-right-click-menus/

    Coincidentally, the controls you mention are among the ones I most often use MenuRighter for. I also use it to add parts of my Personal Macro Workbook to relevant context menus, e.g., I add my Tables sub-menu to the List Range Popup menu.

  2. alexJ says:

    Thanks for the reminder, Doug. In fact I had left a comment on your post! In fact, the mechanism for adding items is the picky part, your tool would be big help there. I’m going to try it again.

  3. Doug Glancy says:

    AlexJ, that’s right, you liked my puns! (which doesn’t happen that often)

  4. Marc J says:

    I’m sorry, but to say there’s no built-in way to customise context menus is just plain wrong. And to use Application-wide CommandBar VBA to customise them should only be used for backwards compatibility with older versions of Excel. Please have a read of https://msdn.microsoft.com/en-us/library/office/ee691832%28v=office.14%29.aspx?f=255&MSPPError=-2147217396

Leave a Reply

Your email address will not be published. Required fields are marked *