Context Menu Commandbar Question

[Sandusky]

Windows XP Pro SP2
Excel 2003 SP3

When I right click anywhere on a worksheet I get the standard Excel context
menu. What commandbar is this, and how would I add a macro item to it?

TIA!

-gk-

 

[JW]

That is the Cells menubar. You add something to it like:
Dim MenuObject As Object
Set MenuObject = Application.CommandBars("Cell"). _
Controls.Add(Type:=msoControlButton)
With MenuObject
.Caption = "Whatever"
.OnAction="Module1.macroName"
End With
Set MenuObject = Nothing

You could then incorporate code to loop though the menu when the
applicable workbook is closed and remove the button, if you so choose.

 

[Dave Peterson]

It's the "Cell" menubar.

Saved from a previous post:

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.

If you only want it to work on a specific workbook, you can put the code in that
workbook.

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).

 

[Sandusky]

Windows XP Pro SP2
Excel 2003 SP3

When I right click anywhere on a worksheet I get the standard Excel
context
menu. What commandbar is this, and how would I add a macro item to it?

TIA!

-gk-

Thank you both! Works like a charm.

Sorry about the double post.

-gk-