How to add a macro to the right click menu?

[yamaharm]

Hi all, this is hopefully a simple question. How can I add the 3 macro's I have defined into the standard right click menu???

 

[Richie(UK)]

Hi yamaharm,

Amend the following as required:

Worksheet_BeforeRightClick
The following EVENT subroutine will invoke the macro named in the cell upon a right click. Since the macro is for a specific worksheet, we can be very specific as to which column this will apply to.&nsp; As in the previous example there are escapes if not in the correct area.

Private Sub Worksheet_BeforeRightClick(ByVal Target _
As Excel.Range, Cancel As Boolean)
'to install -- rightclick on the sheettab of the corresponding
' sheet and choose 'view code'. Paste the following procedure
' in the module.
'RightClick on cell in column C will invoke macro in that cell
If Target.Column <> 3 Then Exit Sub
If Target.Row = 1 Then Exit Sub
On Error Resume Next
'MsgBox ActiveCell.Value
Application.Run ActiveCell.Value
Application.Run "testng2k.xls!" & ActiveCell.Value
Cancel = True 'prevents normal RClick menu from appearing
End Sub

Taken from this site : http://www.mvps.org/dmcritchie/excel/excel.htm

Hope that helps,
Richie

 

[Colo]

Hi yamaharm,
Please copy this into a standard module.
And run "AddToRightClick"

You will need to modify the name of Macro. look @ following code.

<PRE>
Sub AddToRightClick()
With Application.CommandBars("Cell")
.Reset
With .Controls.Add _
(Type:=msoControlButton, temporary:=False)
.Caption = "Macro1" 'change here to name as you like
.OnAction = "Macro1" 'Change here to the your Macro1
End With
With .Controls.Add _
(Type:=msoControlButton, temporary:=False)
.Caption = "Macro2" 'change here to name as you like
.OnAction = "Macro2" 'Change here to the your Macro2
End With
With .Controls.Add _
(Type:=msoControlButton, temporary:=False)
.Caption = "Macro3" 'change here to name as you like
.OnAction = "Macro3" 'Change here to the your Macro3
End With
End With
End Sub

Sub ResetRightClick()
Application.CommandBars("cell").Reset
End Sub
</PRE>

 

[yamaharm]

colo,

Please forgive me for being thick as I have trouble just recording a macro in ecxel. I understand how to edit your code to add my macros but where exactly do I add this code?

Thanks for your help and your patience...

 

[Ivan F Moala]

On 2002-06-12 02:51, yamaharm wrote:
colo,

Please forgive me for being thick as I have trouble just recording a macro in ecxel. I understand how to edit your code to add my macros but where exactly do I add this code?

Thanks for your help and your patience...

Put the code in a STD Module ie.
Alt F11
Select Insert > Module
Copy & Paste code in the new Module

Suggest you call it using workbook open event

eg
<pre/>
Private Sub Workbook_Open()
Call colos_codeto_activate
End Sub
</pre>

Then deactivate via workbook close event

<pre/>
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call colos_codeto_deactivate
End Sub
</pre>