Customizing Context Menus in All Versions of Microsoft Excel

Summary: Learn how to customize context menus in Excel 97 through Excel 2010.

Applies to: Excel | Excel 2010 | Office 2007 | Office 2010 | SharePoint Server 2010 | VBA

Published:   November 2010

Contents

• Overview of Context Menus in Microsoft Office

• Overvew of Context Menus in Excel

• Customizing Context Menus in Excel

• Adding Controls to the Cell Context Menu by Using VBA Code

• Adding Controls to the Cell Context Menu by Using Ribbon Extensibility

• Adding a Dynamic Menu to the Cell Context Menu with VBA code or Ribbon Extensibility

• Context Menu Tips and Tricks

• Summary

• Additional Resources

Overview of Context Menus in Microsoft Office

A context menu (also called contextual or shortcut menus) is a menu that appears following some user interaction, usually as a right mouse click operation. In Microsoft Office, a context menu offers a limited set of choices that are available in the current state, or context, of an application. Usually the available choices are actions related to the selected object such as a cell or a column.

Overvew of Context Menus in Excel

In Microsoft Excel, the most common context menu that people use is the Cell context menu. This is the menu that you see when you right-click a worksheet cell or selection of cells. However, there are many other context menus that you can customize as well. For example, the Row and Column context menus that are displayed when you right-click the row or column headers. Figure 1 shows a Cell context menu in Microsoft Excel 2010.

Figure 1. Cell context menu in Excel 2010

Note

The NameX section displayed at the bottom of the menu is created by the Microsoft add-in discussed later in this article.

Customizing Context Menus in Excel

The only way to customize context menus in Excel 97 through Excel 2007 is to use Microsoft Visual Basic for Applications (VBA) code. You will see an example of this shortly. However, in Excel 2010, you can also change a context menu with the same ribbon extensibility (RibbonX) model that you use to customize the other components of the Microsoft Office Fluent User Interface to include the ribbon and the Backstage view.

One of the advantages of using RibbonX in Excel 2010 to customize context menus is that you can add controls that you cannot add by using VBA. The following list shows the controls that you can add to context menus.

• button

• checkBox

• control

• dynamicMenu

• gallery

• menu

• menuSeparator

• splitButton

• toggleButton

However, it is important to reiterate that you can only use RibbonX to customize context menus in Excel 2010. In other versions of Microsoft Excel, you should use VBA and the CommandBars object.

Adding Controls to the Cell Context Menu by Using VBA Code

The following example adds a custom button, built-in button (Save) and a submenu on top of the Cell context menu.

Note

There are two Cell context menus in Microsoft Excel, One is the standard menu and the second one you see when you are in page break preview mode. Page break preview mode shows you what data appears on each page and enables you to quickly adjust the print area and page breaks. To activate page break preview mode, on the ribbon, click View, and then click Page Break Preview. If you want to change the second type of menu then use the following statement.

Set ContextMenu = Application.CommandBars(Application.CommandBars("Cell").Index + 3)

This information also applies to the Row and Column context menus.

To customize the Cell context menu, add a general module to the VBA project in your workbook. Open the Visual Basic Editor (VBE) by pressing Alt + F11, click Insert and then click Module. Paste or type the following six subroutines into the module. The first macro adds the controls to the Cell context menu. The second macro deletes the controls from the Cell context menu. Notice how tags are added to the controls and then used to delete the controls. The other four macros run when you click the button or one of the three options in the submenu. In this example, the last four macros change the case of any text in a cell.

Sub AddToCellMenu()
    Dim ContextMenu As CommandBar
    Dim MySubMenu As CommandBarControl

    ' Delete the controls first to avoid duplicates.
    Call DeleteFromCellMenu

    ' Set ContextMenu to the Cell context menu.
    Set ContextMenu = Application.CommandBars("Cell")

    ' Add one built-in button(Save = 3) to the Cell context menu.
    ContextMenu.Controls.Add Type:=msoControlButton, ID:=3, before:=1

    ' Add one custom button to the Cell context menu.
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "ToggleCaseMacro"
        .FaceId = 59
        .Caption = "Toggle Case Upper/Lower/Proper"
        .Tag = "My_Cell_Control_Tag"
    End With

    ' Add a custom submenu with three buttons.
    Set MySubMenu = ContextMenu.Controls.Add(Type:=msoControlPopup, before:=3)

    With MySubMenu
        .Caption = "Case Menu"
        .Tag = "My_Cell_Control_Tag"

        With .Controls.Add(Type:=msoControlButton)
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "UpperMacro"
            .FaceId = 100
            .Caption = "Upper Case"
        End With
        With .Controls.Add(Type:=msoControlButton)
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "LowerMacro"
            .FaceId = 91
            .Caption = "Lower Case"
        End With
        With .Controls.Add(Type:=msoControlButton)
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "ProperMacro"
            .FaceId = 95
            .Caption = "Proper Case"
        End With
    End With

    ' Add a separator to the Cell context menu.
    ContextMenu.Controls(4).BeginGroup = True
End Sub

Sub DeleteFromCellMenu()
    Dim ContextMenu As CommandBar
    Dim ctrl As CommandBarControl

    ' Set ContextMenu to the Cell context menu.
    Set ContextMenu = Application.CommandBars("Cell")

    ' Delete the custom controls with the Tag : My_Cell_Control_Tag.
    For Each ctrl In ContextMenu.Controls
        If ctrl.Tag = "My_Cell_Control_Tag" Then
            ctrl.Delete
        End If
    Next ctrl

    ' Delete the custom built-in Save button.
    On Error Resume Next
    ContextMenu.FindControl(ID:=3).Delete
    On Error GoTo 0
End Sub

Sub ToggleCaseMacro()
    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))
    On Error GoTo 0
    If CaseRange Is Nothing Then Exit Sub

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

    For Each cell In CaseRange.Cells
        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()
    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))
    On Error GoTo 0
    If CaseRange Is Nothing Then Exit Sub

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

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

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

Sub LowerMacro()
    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))
    On Error GoTo 0
    If CaseRange Is Nothing Then Exit Sub

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

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

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

Sub ProperMacro()
    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))
    On Error GoTo 0
    If CaseRange Is Nothing Then Exit Sub

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

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

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

Copy the following two event procedures into the ThisWorkbook module of your workbook. These events automatically add the controls to the Cell context menu when you open or activate the workbook and delete the controls when you close or deactivate the workbook.

Private Sub Workbook_Activate()
    Call AddToCellMenu
End Sub

Private Sub Workbook_Deactivate()
    Call DeleteFromCellMenu
End Sub

Next, save, close, and reopen the workbook to see the changes in the Cell context menu. The Cell context menu should now look like the menu shown in Figure 2.

Figure 2. Customized Cell context menu

Adding Controls to the Cell Context Menu by Using Ribbon Extensibility

In the following example, you create the same buttons and submenu as with the example described previously. This time, you use RibbonX to create the controls in Excel 2010. This option is not available in Excel 97 through Excel 2007.

Note

For more information about using the Custom UI Editor and using the RibbonX to customize the Microsoft Office Fluent UI, see Ron’s ribbon website.

The Cell context menu in page break preview mode in Excel 2010 has a different identifier than the regular Cell context menu. You can find this and the IDs of other context menus by using the Microsoft add-in described later in this article.

To add the XML to add controls to the context menu

1. Open a new workbook in Excel 2010 and save it at as a Macro Enabled Workbook (.xlsm).

2. Close the workbook.

3. Open the workbook in the Custom UI Editor (see the previous note for more information), click Insert and then click Office 2010 Custom UI Part.

4. Insert the following XML into the Office 2010 Custom UI Part window.

   <customUI xmlns="https://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>
   

5. Save and close the editor.

6. Open the workbook in Excel 2010.

7. Copy or type the following four macros in the VBE into a general module and save the workbook.

   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
   

8. In the workbook, right-click a cell containing text and see that the Cell context menu has changed.

9. Select the change case options at the top of the context menu to see how it affects the cell text.

Warning

When you close the workbook, every control you added is removed from the Cell context menu. However, if you open another workbook while in the current workbook, the built-in Save button you previously added to the Cell context menu is not removed. This is an issue when you add built-in controls to a context menu. If this is a problem for you, you can insert a custom button, instead of a built-in control, that calls a macro which executes the built-in Save command.

To add a custom button with a built-in command, replace the statement <button idMso="FileSave" insertBeforeMso="Cut" /> with the following statement <button id="DuplicateBuiltInButton1" label="Save" insertBeforeMso="Cut" onAction="BuiltInSaveCommand" imageMso="FileSave"/>. Next, in the VBE, add the following macro called by the onAction attribute.

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

Additionally, you can also use the ActiveWorkbook.Save method. However, by using the ExecuteMso method, you can execute any built-in control on the Microsoft Office Fluent UI.

Adding a Dynamic Menu to the Cell Context Menu with VBA code or Ribbon Extensibility

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.

The following is the RibbonX XML that creates a dynamic menu in the Cell context menu.

<customUI xmlns="https://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.

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

    xml = "<menu https://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

You can download three example workbooks that add a dynamic menu to the Cell context menu based on the username, regional language used in Excel, or the value of the active cell (Formula, Empty, Numeric, or Text). You can find all of the information about the example in the relevant workbook and download the three workbooks from Ron’s website.

Context Menu Tips and Tricks

This section shows you how to modify the code presented in this article to give your procedures more flexibility.

In the VBA example shown at the beginning of this article, you saw how to change the Cell context menu by using the Activate and Deactivate events of the workbook to call a macro to create and delete the menu controls.

You can change the Activate event in the following example to create different menus for different users so that you can create a menu with different sets of custom controls for each user.

Private Sub Workbook_Activate()
    Dim sUserName As String
    sUserName = Application.UserName

    Select Case sUserName
    Case "Ron de Bruin": Call AddToCellMenu
    Case "Nancy Davolio": Call AddToCellMenu2
    Case Else: Call AddToCellMenu3
    End Select
End Sub

Another example is to check for the regional language in Excel in the Activate event so you can create menu captions in the context menu in the language of the user. In the following example, if a Dutch or German user opens the workbook, a different macro is called depending on the regional language setting for the user’s computer.

Private Sub Workbook_Activate()
    Dim LangID As Long
    LangID = Application.International(xlCountryCode)

    Select Case LangID
    Case 31: Call AddToCellMenuInDutch
    Case 49: Call AddToCellMenuinGerman
    Case Else: Call AddToCellMenu
    End Select
End Sub

Note

For more information about international issues, see the International Excel Issues web page at Ron de Bruin’s Web site.

The following VBA statement shows you how to enable and disable the Insert comment control on the Cell context menu in Excel 97 through Excel 2010: Application.CommandBars("Cell").FindControl(ID:=2031).Enabled = False.

Note

The add-in described at the end of this article shows you how to find the ID's of each control on a context menu.

You can also use a macro such as the following to add the ID to the caption. Remember that the Cell context menu is not exactly the same in every version of Excel.

Sub Add_ID_To_ContextMenu_Caption()
' Add the Id to the caption of the context menu controls.
    Dim ctl As CommandBarControl
    For Each ctl In Application.CommandBars("Cell").Controls
        On Error Resume Next
        ctl.Caption = ctl.ID & " ::: " & ctl.Caption
        On Error GoTo 0
    Next ctl
End Sub

Sub Reset_ContextMenu()
' Remove the Id of the caption of the context menu controls.
    Dim ctl As CommandBarControl
    Dim myPos As Long
    For Each ctl In Application.CommandBars("Cell").Controls
        myPos = InStr(1, ctl.Caption, " ::: ", vbTextCompare)
        If myPos > 0 Then
            ctl.Caption = Mid(ctl.Caption, myPos + 4)
        End If
    Next ctl
End Sub

Sub Reset_ContextMenu_To_Factory_Defaults()
    Application.CommandBars("Cell").Reset
End Sub

You can use the Activate and Deactivate events described previously to disable and enable controls.

To disable the Delete control in the Cell context menu with RibbonX, add the statement <button idMso="CellsDelete" enabled="false" /> to the contextMenu control in the XML shown previously in the Adding Controls to the Cell Context Menu by Using Ribbon Extensibility section of this article.

As you can see in this statement, to disable a control on a context menu, you must know the control’s ID (idMso). However, this can be an issue as this information is not available for every control on a particular context menu. For example, this is true of the Cell, Row, and Column context menus. There appears to be no list of control IDs that are supported in RibbonX. You can use a workaround by using VBA code to disable a specific control similar to that described in the previous section.

So how can you find the names of the other context menus that you want to change? The following macro adds a button with the menu name to the bottom of each context menu. It is not possible to see a name on each context menu when you run it in Excel 2007 or Excel 2010. For example, you will not see the menu name when you right-click a shape or picture in Excel 2007 or Excel 2010. Thus, it is not possible to change these menus in Excel 2007 or Excel 2010 with VBA.

Sub Add_Name_To_Contextmenus()
    Dim Cbar As CommandBar
    For Each Cbar In Application.CommandBars
        With Cbar
            If .Type = msoBarTypePopup Then
                On Error Resume Next
                With .Controls.Add(Type:=msoControlButton)
                    .Caption = "Name for VBA = " & Cbar.Name
                    .Tag = "NameButtonInContextMenu"
                End With
                On Error GoTo 0
            End If
        End With
    Next
End Sub

Sub Delete_Name_From_Contextmenus()
    Dim Cbar As CommandBar
    Dim ctrl As CommandBarControl

    For Each Cbar In Application.CommandBars
        With Cbar
            If .Type = msoBarTypePopup Then
                For Each ctrl In .Controls
                    If ctrl.Tag = "NameButtonInContextMenu" Then
                        ctrl.Delete
                    End If
                Next ctrl
            End If
        End With
    Next
End Sub

Use the following links to files with control IDs and control image FaceIds of each control in almost every context menu when you change the context menus with VBA.

• Use the BtnFace add-in from Jim Rech to find every control image FaceId at the following Web Page.

• To find the control IDs and control image IDs, visit the following Web Page from Ole P. Erlandsen.

• Visit John Walkenbach’s Web Page to find every control image ID.

Use the following link to files with the control idMso values or the imageMso values of each control in almost every context menu when you change the context menus with RibbonX.

http://www.rondebruin.nl/ribbon.htm#images

Use the Microsoft COM add-in to find the names of each context menu in Microsoft Office 2010. It adds a button at the end of the context menus. The button will help you to find the name for most context menus.

Summary

This article demonstrates the following points:

• In Excel 97 through Excel 2003, you can add controls to every context menu with VBA code (although this has not been tested for every scenario) but is it not possible to change context menus with RibbonX.

• In Excel 2007, you can add controls to almost every context menu with VBA code. However, it is not possible to change some context menus such as the Shapes and Picture context menu with VBA. In addition, it is not possible to change context menus with RibbonX.

• In Excel 2010, you can add controls to almost every context menu with VBA code. The same restrictions for changing some context menus with VBA as in Excel 2007. Additionally, you can add buttons and menus to every context menu with RibbonX but if you want to disable or re-purpose controls, you may not be able to find the correct IDs (idMso).

Additional Resources

Find more information about the subjects described in this article at the following locations:

• Ron de Bruin Ribbon Web Page

• Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3)