How to customize right-click menu on Excel?

  • Thread starter Thread starter David Loh
  • Start date Start date
D

David Loh

How can I customize the menu that appears when I right-click the mouse
on a cell on Excel worksheet?

For example, I want to add the Auto Sum function on the right-click
menu, which currently contains the default Cut, Copy, Paste, etc.

I have searched this newsgroup but can't seem to find the answer.

Thank you.
 
Hi David,

Right-Click Context Menus in Excel
http://www.mvps.org/dmcritchie/excel/rightxl.htm

Sorry but this site seems to have disappeared so will point
to an archive of the site. via http://www.archive.org/

Customizing Your Right-click Menus, by David Ringstrom
http://web.archive.org/web/20030423...tures/1997/10/vba199710dr_f/vba199710dr_f.asp

As far as the AutoSum code goes, I don't know how to simulate all the
different things it appears to guess at where to put the sum,and
to include or exclude previous sum formulas..
 
I agree, AutoSum is a tough one. Perhaps the code:

CommandBars.FindControl(, 226).Execute

could be attached to the custom menu item.
 
Hi David
in addition find some code below for adding an antry to the menu:

Sub Add_Item()
Dim New_Entry As Object
Set New_Entry = CommandBars("Cell").Controls.Add(Temporary:=True)
On Error Resume Next
New_Entry.Controls("My message").Delete
On Error Goto 0

With New_Entry
.Caption = "My message"
.OnAction = "Message"
End With
End Sub


Sub Message()
MsgBox "Now you code yould start"
End Sub

Sub Delete_Item()
Dim myControl As CommandBarButton
For Each myControl In CommandBars("Cell").Controls
If myControl.Caption = "My message" Then
myControl.Delete
End If
Next
End Sub
 
Or modifying Chip's coding, where the deletion is done automatically
by reset in the auto_open instead of the temporary.

Don't know how long temporary is -- probably simply means not stored
in the toolbars (*.xlb).

Should be able to include the auto_sum button in code.

Sub auto_open()
'-- other codings removed from here blah, blah, blah...

'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"
.OnAction = ThisWorkbook.Name & "!CopyFormula" 'in ChipPearson_Module
.Tag = "Formulas" 'cControlTag
.BeginGroup = True
End With

With .Add
.Caption = "P&aste Formula"
.OnAction = ThisWorkbook.Name & "!PasteFormula" 'in ChipPearson_Module
.Tag = "Formulas2" 'cControlTag
End With
With .Add 'adding AutoSum for David Loh 2004-03-20 worksheet.functions
.Caption = "Auto&Sum"
.OnAction = ThisWorkbook.name & "!Simulate_autosum" 'see macro below
'--need to include the button icon
End With
End With
'instead of Auto_Open use Workbook_Open in the ThisWorklbook
' when you need to fire off a macro when opening with code.
End Sub
Sub Simulate_AutoSum()
CommandBars.FindControl(, 226).Execute
End Sub

'-- in another module in my personal.xls
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
'-----------------------------------------------


FWIW -- I also have the following code in AutoOpen:

If Application.TransitionNavigKeys Then
MsgBox "Found Transition navigation keys, please turn off"
'Application.TransitionNavigKeys = False
End If
Assistant.On = False 'Turn off the bloody Office Assistant - in case reinstalled
'---- do not include following code in your personal.xls Auto_Open
'-- because it would get -- 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 & " <calculation in> " & _
' ActiveWorkbook.FullName
' Application.Calculation = xlAutomatic
' MsgBox Application.Calculation
'End If
 
change
.Caption = "Auto&Sum"
to something like
.Caption = "A&utoSum"
since there already is an "S" shortcut in this menu.
 
Back
Top