Shortcut Menus


Cell, Row and Column

There are actually 2 of these shortcut menus in Excel
The first is used when the worksheet is in Normal view.
The second is used when the worksheet in in Page Break Preview.


CommandBars("Cell").ShowPopup x:=0, y:=0 

Define a shortcut key

Application.OnKey "^+c", "MacroName" 

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean) 
   If Union(Target.Range("A1"), Range("data")).Address = Range("data").Address Then
      CommandBars("myshortcutmenu").ShowPopUp
      Cancel = True
   End If
End Sub

To work with the shortcut menus you need to know the corresponding Caption and Index values
The index values for the shortcut menus have not remained consistent between versions


You should always refer to the Caption for reference


to disable all of the right-click shortcut menus

Sub DisableAllShortCutMenus 
Dim cb as commandbar
   For each cb in commandbars
      If cb.type = msobartypepopup Then cb.enabled = False
   Next cb
End Sub

To disable all of the shortcut menus using the mouse use commandbars("ToolBar List").Enabled = False


It is possible to create an entirely new shortcut menu


Deleting a shortcut menu

CommandBars("Cell").Enabled = False 

Restoring a shortcut menu

CommandBars("Cell").Enabled = False 

Deleting a menu on a shortcut menu

CommandBars("Cell").Controls("Cut").Delete 

Restoring a menu on a shortcut menu

CommandBars("Cell").Controls.Add(Type:=msoControlButton, Id:=21, Before:=1) 

Deleting a submenu on a shortcut menu

CommandBars("PivotTable Context Menu").Controls("Formulas").Delete 

Restores a submenu on a shortcut menu

Dim objControl As Object 
   Set objControl = CommandBars("PivotTable Context Menu")
   objControl.Controls.Add(Type:=msoControlPopup, Id:=30254, Before:=8)
   objControl.Reset

The Reset method is used to populate the Formulas submenu.


Deleting a command on a submenu

Dim objControl As Object 
   Set objControl = CommandBars("PivotTable Context Menu").Controls("Formulas")
   objControl.Controls("Calculated Field").Delete

Restoring a command on a submenu

Dim objControl As Object 
   Set objControl = CommandBars("PivotTable Context Menu").Controls("Formulas")
   objControl.Add(Type:=msoControlPopup, Id:=1597, Before:=1)

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext