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

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]

  context menu
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 FormulaPaste FormulaAutoSumGetFormula.
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
    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
   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
   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"
     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"
   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
    Dim x As New DataObject
    x.SetText ActiveCell.Formula
End Sub

Sub PasteFormula()
    On Error Resume Next
    Dim x As New DataObject
    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.
cell Context
for cell selection
    cell Context Row Menu
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)

Missing the right click context menu for sheet tabs (fix with Ctrl+G)

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

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