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.


<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<contextMenus>
<contextMenu idMso="ContextMenuCell">

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

<button id="MyButton" label="Toggle Case Upper/Lower/Proper" insertBeforeMso="Cut" onAction="ToggleCaseMacro" imageMso="HappyFace"/>

<menu id="MySubMenu" label="Case Menu" insertBeforeMso="Cut" >
<button id="Menu1Button1" label="Upper Case"
imageMso="U" onAction="UpperMacro"/>
<button id="Menu1Button2" label="Lower Case"
imageMso="L" onAction="LowerMacro"/>
<button id="Menu1Button3" label="Proper Case"
imageMso="P" onAction="ProperMacro"/>
</menu>

<menuSeparator id="MySeparator" insertBeforeMso="Cut" />

</contextMenu>
</contextMenus>
</customUI>

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 van use the menu options. Note: imageMso is not working on a Mac.


Sub ToggleCaseMacro(control As IRibbonControl)
Dim CaseRange As Range
Dim CalcMode As Long
Dim cell As Range

On Error Resume Next
Set CaseRange = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
If CaseRange Is Nothing Then Exit Sub

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

For Each cell In CaseRange
Select Case cell.Value
Case UCase(cell.Value): cell.Value = LCase(cell.Value)
Case LCase(cell.Value): cell.Value = StrConv(cell.Value, vbProperCase)
Case Else: cell.Value = UCase(cell.Value)
End Select
Next cell

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub


Sub UpperMacro(control As IRibbonControl)
Dim CaseRange As Range
Dim CalcMode As Long
Dim cell As Range

On Error Resume Next
Set CaseRange = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
If CaseRange Is Nothing Then Exit Sub

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

For Each cell In CaseRange
cell.Value = UCase(cell.Value)
Next cell

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub


Sub LowerMacro(control As IRibbonControl)
Dim CaseRange As Range
Dim CalcMode As Long
Dim cell As Range

On Error Resume Next
Set CaseRange = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
If CaseRange Is Nothing Then Exit Sub

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

For Each cell In CaseRange
cell.Value = LCase(cell.Value)
Next cell

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub


Sub ProperMacro(control As IRibbonControl)
Dim CaseRange As Range
Dim CalcMode As Long
Dim cell As Range

On Error Resume Next
Set CaseRange = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
If CaseRange Is Nothing Then Exit Sub

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

For Each cell In CaseRange
cell.Value = StrConv(cell.Value, vbProperCase)
Next cell

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub

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.


<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<contextMenus>
<contextMenu idMso="ContextMenuCell">
<dynamicMenu id="MyDynamicMenu"
label= "My Dynamic Menu" imageMso="HappyFace"
getContent="GetContent" insertBeforeMso="Cut"/>
</contextMenu>
</contextMenus>
</customUI>

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


Sub GetContent(control As IRibbonControl, ByRef returnedVal)
Dim xml As String

xml = "<menu xmlns=""http://schemas.microsoft.com/office/2009/07/customui"">" & _
"
<button id=""but1"" imageMso=""Help"" label=""Help"" onAction=""HelpMacro""/>" & _
"
<button id=""but2"" imageMso=""FindDialog"" label=""Find"" onAction=""FindMacro""/>" & _
"
</menu>"

returnedVal = xml
End Sub

Sub HelpMacro(control As IRibbonControl)
MsgBox "Help macro"
End Sub

Sub FindMacro(control As IRibbonControl)
MsgBox "Find macro"
End Sub
26-February-2021
Web design by Will Woodgate