Right Click (context menus) have a special interest to me. Being able to create a few such menus for Excel can be a great help.

Shortcut to
Context menu
[Shift+F10]

Right Click Menus (Context Menus) in Excel

Location: http://www.mvps.org/dmcritchie/excel/rightclick.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

This page contains some VBA macros. If you need assistance to install or to use a macro please refer to my «Getting Started with Macros« or delve into it deeper on my Install page.

Setting up Right click menu in Excel

Rather than just referencing postings by Chip Pearson and the corresponding repairs in the same thread, I'll show what I'm actually using. Proper credits shown in the code. As you can see in the picture at the right four entries have been added: Copy Formula, Paste Formula, AutoSum, GetFormula.

background: Shortcut menus, adding to, this particular modification allows for pasting formulas unchanged, Chip Pearson via Drew Paterson -- 2001-04-13 misc. Also see Toolbars page. key phrases: Application.CommandBars("Cell").Controls, !CopyFormula, !PasteFormula, ThisWorkBook, Add(temporary:=True)

In my Auto_Open in my personal.xls (I call mine with a different name, you'll have to change code yourself) (#auto_open)

Option Explicit
Sub auto_open()
  '-- MsgBox "Version" & Application.Version & _
  '   "  <-- should be for version 9 h:\...\personal.xls"
   'Workbooks.Open Filename:="H:\Excel2K\menumakr.xls"
   'ActiveWindow.Visible = False
   If Application.TransitionNavigKeys Then
     MsgBox "Found Transition navigation keys, please turn off"
     'Application.TransitionNavigKeys = False
   End If
  ' Double click on Clippy or his one of his friends.  (Excel 2000)
  ' Unselect everything in sight, and especially "Use the Office Assistant"
  '
  'It has been removed from  Office 2002
  '    http://www.officeclippy.com
    Assistant.On = False   'Turn off the bloody Office Assistant doesn't work in Win98SE
   'Application.Calculation = xlManual
   '---- do not include following code in your personal.xls  Auto_Open
   '--   Run-time error '1004'.
   '--   Method 'Calculation' of object '_Application' failed
   'If Application.Calculation <> -4105 Then
   '    '-4105 automatic, -4135 manual, 2 semi-automatic
   '    MsgBox Application.Calculation & "  " & _
   '       ActiveWorkbook.FullName
   '    Application.Calculation = xlAutomatic
   '    MsgBox Application.Calculation
   'End If

    'The following line is used to turn off prompting in AutoSave Addin in XL2000
    'Result if addin is turned off: Run-time error '9'; subscript out of range
'    Workbooks("autosave.xla").Excel4IntlMacroSheets("Loc Table").Range("ud01b.Prompt").Value = False
    'Jim Rech 1999-09-16, MS has an XL2000 fix for not saving AutoSave settings
    ' http://support.microsoft.com/support/kb/articles/Q231/1/17.ASP
    'download: http://support.microsoft.com/download/support/mslfiles/ASUpdate.exe
    
    
    'Chip Pearson via Drew Paterson -- 2001-04-13 misc
    '--http://groups.google.com/groups?threadm=uiqh89AxAHA.1620%40tkmsftngp05
    Application.CommandBars("Cell").Reset   'was not in 2001-04-13 posting
    With Application.CommandBars("Cell").Controls
     With .Add
        .Caption = "C&opy Formula"  'Copy Formula /  Paste Formula
        .OnAction = ThisWorkbook.Name & "!CopyFormula"  'in ChipPearson_RClick
        .Tag = "Formulas"   'cControlTag
        .BeginGroup = True
     End With

     With .Add
        .Caption = "P&aste Formula"
        .OnAction = ThisWorkbook.Name & "!PasteFormula" 'in ChipPearson_RClick
        .Tag = "Formulas2"   'cControlTag
     End With
     With .Add     'adding AutoSum for David Loh  2004-03-20 worksheet.functions
        .Caption = "A&utoSum"
        .OnAction = ThisWorkbook.Name & "!Simulate_autosum"   'see macro below
        .Tag = "Auto Sum via RtClick"
        '--need to include the button icon
     End With
     With .Add    'Adding  Clear_Constants
        .Caption = "Clear Constants (leave formulas)"
        .OnAction = ThisWorkbook.Name & "!Clear_Constants"  'DMcRitchie_RClick
        .Tag = "Clear_Constants"
     End With
     With .Add    'Adding  EndTotal
        .Caption = "Create SUB&TOTAL at end of column"
        .OnAction = ThisWorkbook.Name & "!endtotal_sub"  'DMcRitchie_RClick
        .Tag = "EndGetFormula"
     End With
     With .Add    'Adding  GetFormula  David McRitchie
        .Caption = "GetFormula"
        .OnAction = ThisWorkbook.Name & "!GetFormula_sub"  'DMcRitchie_RClick
        .Tag = "GetFormula"
     End With
   End With 
   
   '-- there are separate commandbar controls for Row and Column
   Application.CommandBars("Row").Reset
   With Application.CommandBars("Row").Controls
     With .Add    'Adding  Clear_Constants
        .Caption = "Clear Constants in Selected Rows (leave formulas)"
        .OnAction = ThisWorkbook.Name & "!Clear_Constants"  'DMcRitchie_RClick
        .Tag = "Clear_Constants in Rows"
     End With
    End With
    
   Application.CommandBars("Column").Reset
   With Application.CommandBars("Column").Controls
     With .Add    'Adding  Clear_Constants
        .Caption = "Clear Constants in Selected Columns(leave formulas)"
        .OnAction = ThisWorkbook.Name & "!Clear_Constants"  'DMcRitchie_RClick
        .Tag = "Clear_Constants in Columns"
     End With
   End With
   'instead of Auto_Open use Workbook_Open in the ThisWorkbook
   ' when you need to fire off a macro when opening with code.
End Sub

Sub Simulate_AutoSum() 'David Loh  2004-03-20 
   CommandBars.FindControl(, 226).Execute
End Sub

In a separate module which have named "DMcRitchie_RClick" I have the following code:

Option Explicit

'see comments below
Sub GetFormula_sub()
  If ActiveCell.Column = 1 Then
   ActiveCell.Formula = "=personal.xls!GetFormula(" & _
     ActiveCell.Offset(-1, 0).Address(0, 0) & ")"
   MsgBox "couldn't use cell to left so setting to use from cell above"
  Else
     ActiveCell.Formula = "=personal.xls!GetFormula(" & _
     ActiveCell.Offset(0, -1).Address(0, 0) & ")"
  End If
End Sub

Sub EndTotal_sub()
    Dim end_data As Long   'dmcritchie RClick 2005-05-28
   Range(ActiveCell.Address, _
     Cells(Rows.Count, ActiveCell.Column).End(xlUp).Address).Select
   end_data = ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
   ActiveSheet.Cells(end_data + 1, ActiveCell.Column).Formula = _
     "=SUBTOTAL(9," & ActiveSheet.Cells(2, ActiveCell.Column).Address(1, 0) _
     & ":OFFSET(" & ActiveSheet.Cells(end_data + 1, _
     ActiveCell.Column).Address(0, 0) & ",-1,0))"
End Sub

Sub Clear_Constants()     'D.McRitchie RClick 2005-11-19 (also see insrtrow.htm)
   '-- provide for in rightclick  cell, row, and column commandbars
   Dim rng As Range  'prevent expansion of a single cell selection
   Set rng = Intersect(Selection, Selection.SpecialCells(xlConstants))
   If rng Is Nothing Then
     MsgBox "No constants in selection area(s) -- no removal"
   Else
     rng.ClearContents
   End If
End Sub

'change to personal.xls  if that is what you use
'see also  ChipPearson_Module for Chip Pearson's  RClick menus
'these are setup in  module1 in an Auto_Open
'Documentation in:  http://www.mvps.org/dmcritchie/excel/rightclick.htm

In a separate module which have named "ChipPearson_RClick" I have the following code:

Option Explicit Sub CopyFormula() 'Chip Pearson, microsoft.public.excel.worksheet.functions, 2000/05/02 'http://groups.google.com/groups?hl=en&newwindow=1&th=4831aec5cbe19367&rnum=1 'http://groups.google.com/groups?as_umsgid=OWeRetUjBHA.2212@tkmsftngp05 Dim x As New DataObject x.SetText ActiveCell.Formula x.PutInClipboard End Sub Sub PasteFormula() On Error Resume Next Dim x As New DataObject x.GetFromClipboard ActiveCell.Formula = x.GetText End Sub

also see MS KB 159619 - XL97: Sample Macros for Customizing Menus and Submenus

also see Bob Phillips, programming, 2004-05-09

also see Gord Dibben 2002-03-26 to copy a range of formulas without change. (actual author unknown)

Changing Right Click via the Intermediate Window (#CtrlG)

You can add to or remove items from the Right Click menu through the intermediate window. I don't really know why someone would actually choose to include Freeze panes but here is a solution offered by JE McGimpsey, 2004-08-31, if you want the context menu to toggle like in the window menu (freeze/unfreeze) you would need a macro (also supplied in thread). Alternately to unfreeze you can Rclick on A1.
Application.CommandBars("Cell").Controls.Add Id:=443
Application.CommandBars("Cell").Controls("freeze panes").Delete
To find the needed id, the following returns 443 from the Intermediate Window
?Commandbars("Worksheet Menu Bar").Controls( _ "Window").Controls("Freeze Panes").Id
Another method of finding the id is to use Jim Rech's FaceIT addin and use the associated number, BtnFaces, displays the command bar button control faces built into Excel and their associated FaceID numbers. Developers can use the FaceIDs to add faces to their own command bar button controls. Since the same buttons may be used several time you can't really tell if you picked the right one.

CommandBar Controls (#controls)

There are separate commandbars for Cell, Row, and Column which can be seen in the coding for Auto_Open example above. Below are the Context menus for cell and for Row.

for cell selection for row selection

Problems (#problems)

There is no right click menu (context menu missing). Try this command in the Intermediate Window (Ctrl+G) of the VBE (Alt+11)
Application.CommandBars("Cell").Reset
Missing the right click context menu for sheet tabs (fix with Ctrl+G)
Application.CommandBars("Ply").Reset

Related (#related)

If not familiar with installation and use of macros, see Getting Started with Macros and User Defined Functions

Additional items you might want to add to your context menu in Excel

UnSelectActiveCell from selection area, and UnSelectActiveArea from selected areas, by Chip Pearson (2003-04-13, misc).

Right-click Menus in Excel

Right click Event macros in Excel. (on my EVENT Macros page)
Customizing Your Right-click Menus [archived], David Ringstrom (msofficemag formerly OfficeVBA.com), Customization of both the "Cell" and "Ply" commandbar menus, (sample code).
Setting up a right-click menu, replies by Bernie Deitrick and Paul B., examples
Listing right-click menus see right-click menus on collections page.
Right-Click, 213757 - XL2000: How to Turn off Shortcut Menus -- cutomization of "CELL" and "PLY" menus,
Customize, 213209 - XL2000: Sample Macros that Customize and Control Shortcut Menus using the Commandbar object

Bookmarklets for Internet Explorer, Netscape, Mozilla, Firefox regain control
over unwanted webpage features like background images, bad CSS coloring schemes, and do neat things like sort HTML Tables (Mozilla) -- see below.
Collections, also includes some additional Right Click Menu things. Also see Bar Hopper to extract information and hierarchy of user created menues.
Excel shortcut keys
Internet Explorer context menus, rather in depth examples of context (right-click) menus that can be found or added to Internet Explorer. also see the Related area on that page.
Menus In Excel97 And 2000: manipulation both manually and in VBA, Chip Pearson.
Menus, User Interface Nick Hodge, understanding Excel through it's menus.
Toolbars, Custom Buttons and Menus
QuickLaunch context menus (Windows), applies also to desktop shortcuts and Start Menu.

Shortcuts, clicks, in Excel and other applications: Right-click: Excel (this page), IE; Firefox(Mozilla); Windows QuickLaunch, Desktop, Start menu; Mouse Operations: Excel, Firefox(Help); Short Cuts (keyboard shortcuts): Excel; IE, OE, Win2000; Toolbars: Excel; Drag/Fillhandle: Excel Fill Handle, Windows drag files; Accessibility Options: MouseKeys; Event Macros: Excel

Bookmarklets; There are a lot of links on this page. The following BookMarklets (Favelets) will distinguish between internal or external links. First click on "highlight links", and then on "int/ext links". Your browser's Reset button (F5) will reload the original unmodified web page.
Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions. Posting suggestions and netiquette. More information on newsgroups and searching newsgroups. Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).
This page was introduced on June 02, 2004. [My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved