Customising Context Menus | Mac Excel Automation

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.

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

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.