This post is a follow-on to my previous Creating an AddIn in Excel post which explains what an AddIn is, what would you use one for, how to create an AddIn and how to handle events with an AddIn. If you are new to AddIns then I recommend you follow that post in full before following this post. Whether you do that or not, to follow along with this post, you should first create a new AddIn as per the instruction in the 'How to create an AddIn?' section of that post … for this example, though, I am naming the AddIn file 'Context Menu AddIn 1.xlam'. Remember to load the AddIn into Excel (using the 'Add-ins available' dialog) once you have saved it otherwise you won't find it in the VBE!
What is a context menu
Context menus are also known as command bars, shortcut menus and popup menus. They are the menus that, typically, appear when you right-click something in Excel. For example, if you right-click in a cell in a Sheet, you may see something like this:
In this post, I am going to show you two ways to customize that menu: programmatically and by using RibbonX.
The final item in the image above is 'VBE_Extras Helper' which allows you to jump from Shapes, Tables and (in this case) Named Ranges in the Excel UI to references of those Shapes, Tables and Named Ranges in your VBA code. VBE_Extras Helper uses the exact process detailed in Using RibbonX to customize the context menu, below, to add its items to a number of context menus. Learn more at Shapes, Tables and Names (Excel only).
Programmatically customize the context menu
This way of customizing context menus has really been superseded by use of RibbonX … but I wanted to show how to do this so that it can be used as a fallback option if using RibbonX isn't possible.
The code is commented for your understanding so should be fairly self-explanatory however, some points to note:
This example adds 3 buttons to the cell context menu – one button is added directly and two other buttons within their own mini sub-menu
The Auto_Open() and Auto_Close() events (see the Creating an AddIn in Excel post for more on these) are used to add and remove the context menu customizations when the AddIn is loaded (typically when Excel starts) and is unloaded (typically when Excel closes)
Excel's name for the cell context menu is "Cell" (later in this post is a method for identifying the names of other context menus)
It's really important to remove all customizations BOTH when Excel is closing AND when it is starting (for example, if Excel crashed and didn't close 'cleanly' the customizations may not have been removed when closing) hence AddToCellMenu(), before doing anything else, first calls DeleteFromCellMenu()
The buttons are deleted by way of identifying if their Tag property matches a specific value (in this case the value held by the constant msTAG) … the value of the Tag property should be unique across all loaded Workbooks (and AddIns) and so, to avoid the possibility of conflict, if you are sharing code with others then you should change this constant to have a (new) unique value (hence the 'TODO' comment at the start of the CommandBarCallbacks Module … to get a list of all such TODOs in your VBA project, see VBE_Extras)
The names used in the OnAction property of each button must match exactly to the name of a Sub … the Sub's don't have to be in the same Module that the buttons are added in (I just did that for convenience) but they must be in a 'standard' Module
There are a wide range of 'FaceIds' you can use for the images on the buttons … likely far better ones than I used here … to explore 'FaceIds' further, see my post VBA and FaceIds
In each of the button 'handler' Subs, I have used On Error Resume Next. While it SHOULD only be possible for these Subs to be called via a valid selection of one or more cells, this is Excel … created by Microsoft … need I say more?
Here's the code … in a standard Module named App:
And in another standard Module named CommandBarCallbacks:
To get this all working, you can either:
Close and re-open Excel, or
Unloaded and re-load the AddIn, or
Manually run Auto_Open()
… then go to a Sheet in any Workbook, right-click in a cell (or select many cells and right-click in them) and then select one of the custom buttons (obviously, to test the Bold / Unbold buttons then those cells need to have some text in them otherwise you will not see a change after clicking the button!)
I mentioned that this is the old way of customizing context menus so I'm not going to go into further detail, but if you want to explore this further the Microsoft documentation for the CommandBar object is here https://learn.microsoft.com/en-us/office/vba/api/office.commandbar and from that link you will see in the menu the documentation for related objects such as CommandBarControl.
One final tip for this section of the post is this. There are many context menus to customize in Excel and the names are often not obvious. If you identify a context menu that you want to customize and need to know its name then add this code to a standard Module in a normal Workbook (not your AddIn):
Run AddNamesToAllContextMenus() which adds a new item to the end of every context menu with the name of that context menu, like this:
Once you've identified the name of the context menu(s) you are interested in, you can then run DeleteNameFromAllContextMenus() to delete all of the 'Name for VBA' menu items.
Using RibbonX to customize the context menu
Using RibbonX is, overall, much simpler but you should note:
It is only possible to customize context menus using RibbonX in Excel 2010 or newer
You will need to download the Office RibbonX Editor application in order to create and update the RibbonX
Finding the right name for the context menu that you want to customise can be a little "hit-and-miss" ... more details below
First, if you are not at all familiar with RibbonX then you might want to first read my Adding a Button (and a ComboBox) to the Ribbon post which explains some of the basics and provides you with the link to download the excellent (and free) Office RibbonX Editor application which you will need in order to follow along with this section of the post.
So before we can add any RibbonX, you need an AddIn to add it to. You can just re-use the AddIn that you created for the above Programmatically customize the context menu section but I think it's cleaner (and gives you a better idea of how much simpler it is to use RibbonX) to use a new AddIn … so do that by again following the instruction in the 'How to create an AddIn?' section of the Creating an AddIn in Excel post … for this example, though, I am naming the AddIn file 'Context Menu AddIn 2.xlam'. Again, remember to load the AddIn into Excel (using the 'Add-ins available' dialog) once you have saved it otherwise you won't find it in the VBE.
Having done that, close Excel entirely so that the AddIn file is not in use (remember: AddIns are loaded by Excel when it opens and will stay loaded until Excel is closed ... alternatively, you can unloaded the AddIn and then re-load it after the RibbonX has been added) otherwise Office RibbonX Editor application will not be able to save the RibbonX that you are going to add.
Open the Office RibbonX Editor application then use it to open the AddIn file ('Context Menu AddIn 2.xlam' or whatever you decided to call it). Having opened the AddIn, you won't see much at first:
Select the file name in the left-hand panel then, in the menu, select: Insert > Office 2010+ Custom UI Part. When adding context menus, you must use 'Office 2010+ Custom UI Part' and not 'Office 2007 Custom UI Part' … while the majority of RibbonX functionality was added in Office 2007, the ability to use RibbonX to customise context menus was only added in Office 2010.
Then double-click 'customUI14.xml' and add the following RibbonX:
Having done that, the Office RibbonX Editor application should look like this:
Click on 'Validate' to check that the RibbonX is correctly formed (if not, please go back and carefully check the above steps), then 'Save' and then 'Generate Callbacks' and copy the text in the 'Callbacks viewer' dialog. You can then close this dialog and close the AddIn in file in the Office RibbonX Editor application.
If this is all going a bit fast and you'd like more explanation, see the 'Adding a Button' section of my Adding a Button (and a ComboBox) to the Ribbon post.
Next, start Excel and go into the VBE. In the Project Window, expand the Project for the AddIn and add a standard Module and rename it to RibbonCallbacks (to match the name used, before the '.' in the OnActions in the above RibbonX ... there is nothing special about the name "RibbonCallbacks", it can be anything you want so long as it matches what you have used in the RibbonX and the Module itself must be a standard Module).
What you should do now is to paste in the callbacks code that you copied in the 'Callbacks viewer' dialog and then update the comments and code contained in the callbacks to match the following (note the comments and code contained within the callback procedures are identical to that used in the above Programmatically customize the context menu section but the procedure signatures are different). However, you can just copy and paste the following if you want ... but when doing this 'for real' for yourself, you will use the callbacks code that you copied from the 'Callbacks viewer' dialog of the Office RibbonX Editor.
I've deliberately kept the look and the functionality as close as possible to that used in the Programmatically customize the context menu section so that you can compare the two ways of adding context menus.
Which do you reckon is simplest: customizing programmatically or using RibbonX? … so long as you can spend a little time familiarising yourself with the RibbonX then no contest ... personally, I haven't done this the programmatic way for years (at least not until writing this post).
As well as not needing the code to programmatically add and delete the buttons, the other key differences when using RibbonX are:
The same context menu has a different name when you use RibbonX … with RibbonX we add the buttons to 'ContextMenuCell' whereas when doing this programmatically it was 'Cell' … if you want to customize a different context menu then see Getting context menu names for RibbonX, below
FaceIds are used to identify icons when customizing programmatically but an imageMso is used to add built-in icons when using RibbonX … to learn more about icons when using RibbonX (including how to use your own custom icons), see the 'Customising the icon' section of my Adding a Button (and a ComboBox) to the Ribbon post
So how else can you customize the context menus using RibbonX?
Built-in controls
Once you have identified the name of a built-in control within a context menu (see Getting context menu names for RibbonX, below), you can:
Disable a built-in button (in this case, the "Cut" button):
Hide a built-in button (in this case, the "Cut" button):
Add a built-in button (in this case, the "Open" button is added above the "Cut" button):
or (in this case, the "Open" button is added below the "Cut" button)
Try each of the above out by adding them to the RibbonX in a new line immediately below the line that starts with the contextMenu tag and before the first comment line (comments in RibbonX follow the XML convention of starting with <!-- and ending with -->). As usual, you will have to close Excel (or unload the AddIn) before you can save your RibbonX changes.
Other custom controls
As well as adding buttons and menus as we did in the above RibbonX, you can add the following types of control:
checkBox
dynamicMenu
gallery
menuSeparator
splitButton
toggleButton
The documentation to assist you in doing this is here https://learn.microsoft.com/en-us/openspecs/office_standards/ms-customui/846e8fb6-07d3-460b-816b-bcfae841c95b (this is actually the link to the documentation for a button … in the menu on the left you will see links to the documentation for the other types of control listed above)
And this is the link to the 'official' (yes, its Microsoft!) documentation for customising context menus: https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2010/ee691832(v=office.14) … it does include a couple of things not included in this post so is worth a read though, as is often the case with Microsoft documentation, it is (very) far from being comprehensive!
Getting context menu names for RibbonX
The source I have used for a number of years for the context menu names for use in RibbonX is here https://github.com/OfficeDev/office-fluent-ui-command-identifiers/tree/main. Once at this page:
Click the version of Office that you want to get context menu names for (the names don't … or shouldn't! … change once added in an Office version so if you want to ensure backwards compatibility then choose the oldest version of Office you want to work with … as the page only includes the versions of Office that "are currently under mainstream support" then this only goes back to Office 2013 (though support for Office 2013 actually ended in April 2023) - but I would guess very few context menus were added from Office 2010 to Office 2013 and, anyway, as Office 2010 is out of support then no one should really be using it anyway!)
If you clicked Microsoft 365 then select the relevant channel
Click on 'excelcontrols.xlsx'
Find the 'Download raw file' button (in the banner just above 'View raw', it has a downwards pointing arrow on it) and click it
Open the downloaded file
In the 'ControlType' column, filter by 'contextMenu'
… as the data source isn't under my control then I can't provide any guarantee that all context menu names are included or are accurate … but it does seem to be pretty solid data
… it isn't only context menus included in the file you have just downloaded … you will see the names for built-in tabs, groups, buttons, galleries and much more though there is no obvious way to get, for example, the names of all controls on a given context menu so there is often a degree of trial-and-error involved to work out what name relates to what control.
Comments