Customising Context Menus with RibbonX


The most common Context Menu that most people know and use is the Cell menu. This is the menu that you see when you right click on a worksheet cell or selection.

contextmenu1

Note: In Mac Office 2016 and higher there are problems with changing context menus with VBA code, RibbonX is the correct way to do it in the Mac 2016 and higher versions. RibbonX is not working in Mac Office 2011, you need VBA code in this version.

The advantage of RibbonX is that you can add controls that are not possible to add with VBA, the following lists the controls allowed in context menus.

  • control
  • button
  • checkBox
  • dynamicMenu
  • gallery
  • menu
  • menuSeparator
  • splitButton
  • toggleButton

Example to change the Cell menu in Win and Mac Excel


Note: If this is all new for you read the information on this page first how to add RibbonX to a workbook: Change the Ribbon

Open a new workbook and save it at as a Macro Enabled Workbook (xlsm) and close the workbook. Open the file in the Custom UI Editor and Insert an Office 2010+ Custom UI Part. Add the RibbonX below in the Office 2010+ Custom UI Part of the workbook and save it. Close the Custom UI Editor.


Open the workbook in Excel now and copy the four macros below into a Standard module of your workbook and save the file. Now right click on a cell and if you followed the steps correctly you will see that the Cell menu is changed and you can use the menu options. Note: imageMso is not working on a Mac.


Bug: When you close the workbook every control you add is removed from the Cell menu, but if you select another workbook the built-in Save button that we added to the Cell menu is not removed. This seems to be a problem when you add built-in controls to a context menu.

If this is a problem for you you can insert a custom button instead that calls a macro that executes the built-in Save control in this example as a workaround. Replace this line in the RibbonX that added the Built-in control

<button idMso="FileSave" insertBeforeMso="Cut" />

with this

<button id="DuplicateBuiltInButton1" label="Save" insertBeforeMso="Cut" onAction="BuiltInSaveCommand" imageMso="FileSave"/>

The macro for the onAction looks like this

Sub BuiltInSaveCommand(control As IRibbonControl)
CommandBars.ExecuteMso "FileSave"
End Sub


For this example we can also use ActiveWorkbook.Save, but with ExecuteMso you can execute every built-in control on the Ribbon or context Menu.


Add a Dynamic menu to the Cell menu with RibbonX and VBA code

Dynamic menus point to callback procedures that create the menus at runtime. The dynamicMenu control includes the getContent attribute that points to a GetContent callback procedure.

This is the RibbonX in custumUI14.xml that create a dynamic menu in the Cell context menu.


For example, the following VBA code builds a dynamic menu at runtime with two buttons, which means that it is not created until the menu control on the context menu is clicked. You can now add buttons to the VBA code without changing the RibbonX. Or check the UserName or language and use VBA select case to use the correct xml string to build the menu. Note: imageMso is not working on a Mac.

05/09/2024
Web design by Will Woodgate