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
Open a new workbook in Excel 2010 and save it at as a Macro Enabled Workbook (.xlsm).
Close the workbook.
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.
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>
Save and close the editor.
Open the workbook in Excel 2010.
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
In the workbook, right-click a cell containing text and see that the Cell context menu has changed.
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: