Customizing Context Menus in Microsoft Excel

Ron de Bruin (last update 7-Feb-2011)
Go back to the Excel tips page

Contents

Introduction Context Menus
Add button and menu to the Cell menu with VBA code(Every Excel version)
Add button and menu to the Cell menu with RibbonX (Only Excel 2010)
Add a dynamic menu to the Cell menu with VBA code or RibbonX
Disable or Enable a control on the Cell menu with VBA and RibbonX
Tips for the VBA and RibbonX examples
Other Articles
Conclusion

Introduction Context Menus

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.

The screenshot below is from the Excel 2010 Cell menu.
Note: On the bottom of the menu you also see the name of the idMso of this
menu added by the Microsoft add-in discussed later in this article.

The only way to change this menu up to Excel 2007 is to use VBA code, but in Excel 2010
you can also change a context menu with RibbonX.

The advantage of RibbonX in Excel 2010 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

But this will only work in Excel 2010, so if not all your users use Excel 2010 it is better to use
VBA code and add controls that work in every Excel version.

Add button and menu to the Cell menu with VBA code(Every Excel version)

This example will add a custom button, built-in button (Save) and a Custom menu on top of the Cell menu. Other context menus that you can change are the Row and Column context menus for example.
These are the menus that you see when you right click on the row or column headers.
See theTips section for a tip how to find the names of the other context menus.

Note: There are two Cell menu's in Excel, the second one you see when you are in page break preview mode.
If you want to change this menu use this then in the code.
Set ContextMenu = Application.CommandBars(Application.CommandBars("Cell").Index + 3)
The same applies to the Row and Column context menus.

Copy the six macro's below into a General module of your workbook.

If you do not know where to copy the code check out this page.
http://www.rondebruin.nl/code.htm

The first macro adds the controls to the Cell menu (see how I add a Tag to the controls I add).
The second macro deletes the controls from the Cell menu (See how I use the Tag to delete the controls).

The other four macros will run when you click on the Button or on one of the three options in the menu.
As a example I use macros that change the Case of the text cells in the selection.

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

    'Delete the controls first to avoid duplicates
    Call DeleteFromCellMenu

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

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

    'Add one custom button to the Cell 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 custom menu 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 seperator to the Cell menu
    ContextMenu.Controls(4).BeginGroup = True
End Sub



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

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

    'Delete 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 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 two event procedures below in the Thisworkbook module of your workbook
This will automatically add the controls to the Cell 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

Then save, close and reopen the workbook to see the changes in the Cell menu.

Add button and menu to the Cell menu with RibbonX (Only Excel 2010)

We will create the same menu as the VBA example above with the RibbonX below.
If you do not know how to add RibbonX to your workbook see this page
http://www.rondebruin.nl/ribbon.htm

The Cell menu in page break preview mode in Excel 2010 hase a different name.
I am glad that Microsoft use a different idMso now in the RibbonX, see the Add-in in the
Tips part of this article for a easy way to find the idMso's for every context menu.

Open a new workbook and save it at as a Macro Enabled Workbook (xlsm)
Close the workbook in Excel
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
Copy the four macros below into a General 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.

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 VBA code or RibbonX

Excel 2010

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

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

You can download three example workbooks here that add a dynamic menu to the cell menu based
on the username, language of Excel or the value of the active cell (Formula, Empty, Numeric or Text)
You can find all the information about the example in the relevant workbook.

Note: Remember that the RibbonX examples only work in Excel 2010

Download workbooks for Excel 2010

Excel 97-2010

In the VBA example on top of this page I showed you how to change the Cell menu and I used the
Activate and Deactivate event of the workbook to call a macro to create and delete the menu controls.

You can change the Activate event like this to create different menus for different users, this way
you can create a menu with different sets of custom controls for different users.
If you use the same Tags in the code there is no need to call a different macro to delete the menu.

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

    Select Case sUserName
    Case "Ron de Bruin": Call AddToCellMenu
    'Case "Dave Thomson": Call AddToCellMenu2
    'Case Else: Call AddToCellMenu3
    End Select
End Sub

In the example above you will see no changes in the menu if you are not Ron de Bruin.
See the two commented lines that show you how to call a different macro if another user opens the workbook.

Another example is to check for the Excel language in the activate event so you can create menu captions in the language of the user in the context menu. In this case if a Dutch or German user opens the workbook
no menu will be created because I commented out the two lines that call the macros you could make for these two languages. For all other languages in this example the macro AddToCellMenu is called and it creates a menu with English captions

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

For more information about International issues visit :
http://www.rondebruin.nl/international.htm

Disable or Enable a control on the Cell menu with VBA and RibbonX

VBA example for Excel 97-2010

To disable a control use always the ID and not the caption in your code.
If you use the caption, your code will not work when your workbook is used
in a different language version of Excel.

This will disable the Insert comment control for example on the Cell menu in Excel 97-2010.
Application.CommandBars("Cell").FindControl(ID:=2031).Enabled = False

But how do you find the ID's of each control on a contextmenu ?
You can use the add-in from Ole P. Erlandsen that you can find in the Tips section below.

Another way is to use a macro like this that adds the ID to the caption.
Remember that the Cell menu is not exactly the same in every Excel version.

Sub Add_ID_To_ContextMenu_Caption()
'Works in Excel 97 - 2010
'Add the Id to the caption of the contextmenu 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()
'Works in Excel 97 - 2010
'Remove the Id of the caption of the contextmenu 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, like I showed you in the example that added the buttons
and menu to the Cell menu, also to disable and to enable controls.

For more information about Command bars and controls in Excel 97-2003 visit also this page
Disable Command bars and controls in Excel 97-2003
http://www.rondebruin.nl/menuid.htm

RibbonX example for Excel 2010

For example if we want to disable Delete in the Cell menu we can add this to the RibbonX example above.
<button idMso="CellsDelete" enabled="false" />

You see that if we want to disable a control on a contextmenu we must know it's idMso.
But we have a problem because this info is not available for every context menu.
For example for the Cell, Row and Column menu there is no information.

This is the reason :
MS only support a small set of controls in RibbonX, some controls that are available in some context
menus are controls that carried over from legacy versions (e.g. O12) so they don’t support them for extensibility through RibbonX. In some cases you can use the same name’s that are used for the Ribbon,
but is not guaranteed that this will work with every control.

I have try to find Insert Comment that I used in the VBA example but no luck.
As far as I know there is no list of controls that are supported in RibbonX.
But you can always use VBA code example above if you can't find a specific control ID.

Note: See the RibbonX tips section below for a link to a file with every control idMso of
each control in almost every ContextMenu.

Tips for the VBA and RibbonX examples

VBA example tips for Excel 97-2010:

How do I find the name of the contextmenu that I want to change ?
The example macro below will add button on the bottom of each contextmenu with the menu name.
You will notice that you will not see a name on each context menu when you run it in Excel 2007-2010.
For example, you will not see it when you right click on a shape or Picture in Excel 2007-2010.
So it not seems to be possible to change these menus in Excel 2007-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

How do I find the control ID's or FaceID's :

Use the BtnFace add-in from Jim Rech to find every FaceId
Download it from my site

If you want to find the ID's and FaceId's see this page from Ole P. Erlandsen
http://erlandsendata.no/?p=2694

Check out this page from John Walkenbach to find every FaceId
http://spreadsheetpage.com/index.php/site/tip/identifying_commandbar_images/

RibbonX example tips for Excel 2010:

Add-in:

You see that I use "ContextMenuCell" as idMso for the Cell menu
But how do you find the names of the contextmenus ?

Microsoft ContextMenu Add-in for Office 2010
This is an Office 2010 (Excel, Outlook, PowerPoint, Project, Publisher, Word) COM Add-in.
It adds a button at the end of the context menus. The button should help you to find the NameX for that particular context menu.This is a very easy way for developers to find the idMso of the contextmenu.
http://code.msdn.microsoft.com/contextmenusaddin

If you want to know what the add-in's options in backstage view are about, this is the answer :
By default the add-in is packaged with XML files containing the information about the context menus in each application.The options in the backstage are to override the packaged XML’s with updated or custom XML files.

Useful files:

I used this for example in the RibbonX to tell where I wanted the controls
insertBeforeMso="Cut"
Or if you want to insert a built-in control you must use the idMso like: idMso="FileSave"

If you are looking for imageMso values or the control idMso values see the downloads on my Ribbon page :
http://www.rondebruin.nl/ribbon.htm#names
and
http://www.rondebruin.nl/ribbon.htm#images

The only problem is that you can't find the idMso's of the controls of every context menu.

MS only supports a small set of controls in RibbonX, some controls that are available in some context menus are controls that carried over from legacy versions (e.g. O12) so they don’t support them for extensibility through RibbonX. In some cases you can use the same name’s that are used for the Ribbon,
but is not guaranteed that this will work with every control.

This is not good if you want to add your control after a specific built-in control and can't find the correct
idMso or want to re-purpose or disable one of the controls and not know it's idMso.

Other Articles

Customizing Context Menus in All Versions of Microsoft Excel
http://msdn.microsoft.com/en-us/library/gg469862.aspx

Customizing Context Menus in Office 2010 (MSDN: Frank Rice)
http://msdn.microsoft.com/en-us/library/ee691832.aspx

Creating a PopUp Menu that is working in every Excel version
http://www.rondebruin.nl/popup.htm

Conclusion

Excel 97-2003
You can add controls to every context menu with VBA code (though I have not tested them all)
Not possible to change context menus with RibbonX

Excel 2007
You can add controls to almost every contextmenu with VBA code
Not possible to change for example the Shapes and Picture contextmenu with VBA
Not possible to change Context menus with RibbonX

Excel 2010
You can add controls to almost every contextmenu with VBA code
Not possible to change for example the Shapes and Picture toolbar with VBA
But you can add buttons/menus to every ContextMenu with RibbonX but if you want
to disable or re-purpose controls you may not be able to find the correct idMso(read this)