Modifying Built-in Menus

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I would like to modify the menu which appears when I right-click a cell.
Any help on this would be wonderful.
Thanks in Advance.
 
For a specific worksheet or for all worksheets in all workbooks?

Saved from a previous post to do it for all worksheets in all workbooks:

You can modify that rightclick popup in code.

If you want this functionality available for every workbook you open, you can
put the code into a workbook that opens each time excel opens.

Most people would use a file by the name of personal.xls and store it in their
XLStart folder.

This is what the code could look like:

Option Explicit
Sub auto_open()
With Application.CommandBars("cell")
On Error Resume Next
.Controls("Print Selection").Delete
On Error GoTo 0

With .Controls.Add(Type:=msoControlButton, temporary:=True)
.BeginGroup = True
.Caption = "Print Selection"
.OnAction = "'" & ThisWorkbook.Name & "'!PrintMySelection"
End With
End With
End Sub
Sub auto_close()
With Application.CommandBars("cell")
On Error Resume Next
.Controls("Print Selection").Delete
On Error GoTo 0
End With
End Sub
Sub PrintMySelection()
If Selection.Cells.Count = 1 Then
Beep 'why print one cell?
Else
'save paper while testing
Selection.PrintOut preview:=True
End If
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

This code goes in a general module in either case (personal.xls or the specific
workbook).
 
Back
Top