How to Customize Excel 2016 Shortcut Menus in VBA - dummies

How to Customize Excel 2016 Shortcut Menus in VBA

By John Walkenbach

Before Excel 2007, VBA programmers used the CommandBar object for creating custom menus, custom toolbars, and custom shortcut (right-click) menus. Beginning with Excel 2007, the CommandBar object is in a rather odd position. If you write code to customize a menu or a toolbar, Excel intercepts that code and ignores many of your commands.

Instead of displaying your well-thought-out interface enhancement, Excel 2007 (like later versions) simply dumps your customized menus and toolbars into a catch-all Ribbon tab named Add-Ins.

Menu and toolbar customizations end up in the Add-Ins  → Menu Commands or the Add-Ins  →  Custom Toolbars group. But customizing shortcut menus (which also uses the CommandBar object) still works as it always has — well, sort of.

Bottom line? The CommandBar object is not very useful anymore, but it remains the only way to customize shortcut menus.

Adding a new item to the Cell shortcut menu

Below, you will find sample code that adds a new item to the shortcut menu that appears when you right-click a cell. You should be able to adapt these examples to your needs.

You can enhance that the Change Case utility a bit by making it available from the Cell shortcut menu.

The AddToShortcut procedure adds a new menu item to the Cell shortcut menu. You can adapt it to point to your own macros by changing the Caption and OnAction properties of the object named NewControl.

Sub AddToShortCut()
  Dim Bar As CommandBar
  Dim NewControl As CommandBarButton
  Set Bar = Application.CommandBars(“Cell”)
  Set NewControl = Bar.Controls.Add _
     (Type:=msoControlButton, ID:=1, _
  With NewControl
    .Caption = “&Change Case”
    .OnAction = “ChangeCase”
    .Style = msoButtonIconAndCaption
  End With
End Sub

When you modify a shortcut menu, that modification remains in effect until you restart Excel. In other words, modified shortcut menus don’t reset themselves when you close the workbook that contains the VBA code. Therefore, if you write code to modify a shortcut menu, you almost always write code to reverse the effect of your modification.

The DeleteFromShortcut procedure removes the new menu item from the Cell shortcut menu:

Sub DeleteFromShortcut()
  On Error Resume Next
  Application.CommandBars(“Cell”).Controls _
    (“&Change Case”).Delete
End Sub

This shows how the new menu item displayed after you right-click a cell.

The Cell shortcut menu showing a custom menu item: Change Case.
The Cell shortcut menu showing a custom menu item: Change Case.

The first actual command after the declaration of a couple of variables calls the DeleteFromShortcut procedure. This statement ensures that only one Change Case menu item appears on the shortcut Cell menu. Try commenting out that line (put an apostrophe at the start of the line) and running the procedure a few times — but don’t get carried away!

Right-click a cell, and you can see multiple instances of the Change Case menu item. Get rid of all the entries by running DeleteFromShortcut multiple times (once for each extra menu item).

Finally, you need a way to add the shortcut menu item when the workbook is opened and to delete the menu item when the workbook is closed. Doing this is easy. Just add these two event procedures to the ThisWorkbook code module:

Private Sub Workbook_Open()
Call AddToShortCut
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteFromShortcut
End Sub

The Workbook_Open procedure is executed when the workbook is opened, and the Workbook_BeforeClose procedure is executed before the workbook is closed. Just what the doctor ordered.

What’s different in Excel 2013 and Excel 2016?

If you’ve used VBA to work with shortcut menus in Excel 2007 or earlier, you need to be aware of a significant change.

In the past, if your code modified a shortcut menu, that modification was in effect for all workbooks. For example, if you added a new item to the Cell right-click menu, that new item would appear when you right-clicked a cell in any workbook (plus other workbooks that you open later on). In other words, shortcut-menu modifications were made at the application level.

Excel 2013 and Excel 2016 use a single document interface, and that affects shortcut menus. Changes that you make to shortcut menus affect only the active workbook window. When you execute the code that modifies the shortcut menu, the shortcut menu for windows other than the active window will not be changed. This is a radical departure from how things used to work.

Another twist: If the user opens a workbook (or creates a new workbook) when the active window displays the modified shortcut menu, the new workbook also displays the modified shortcut menu. In other words, new windows display the same shortcut menus as the window that was active when the new windows were opened.

Bottom line: In the past, if you opened a workbook or add-in that modified shortcut menus, you could be assured that the modified shortcut menus would be available in all workbooks. You no longer have that assurance.