Call Macro using right click menu

Dear all,

I want to write a macro program so that when right click at on the
worksheet, an item is added to the right-click menu with the name of macro
function, so that the users can call the macro program by right clicking the
worksheet. On the other hand, other regular items such as "Copy",
"Paste",..,etc., should be preserved on the right-click menu.
Can anyone advise how to do this programatically? Thanks in advance!

Ivan

Why not just add a command button with the macro assigned
----------------
Ben

One way:

Option Explicit
Sub auto_close()
Call CleanUpPly
End Sub
Sub auto_open()

Dim iCtr As Long
Dim myMacros As Variant
Dim myCaptions As Variant

Call CleanUpPly

Dim cb As CommandBar
Set cb = Application.CommandBars("ply")

myMacros = Array("mac1", "mac2", "mac3")
myCaptions = Array("Cap 1", "Cap 2", "Cap 3")

With cb.Controls
For iCtr = LBound(myMacros) To UBound(myMacros)
With .Add(Type:=msoControlButton, temporary:=True)
.Caption = myCaptions(iCtr)
.OnAction = "'" & ThisWorkbook.Name & "'!" & myMacros(iCtr)
.Tag = "__myPlyMacs__"
End With
Next iCtr
End With

End Sub
Sub CleanUpPly()
Dim ctrl As CommandBarControl

On Error Resume Next
Do
Set ctrl = Application.CommandBars("Ply") _
.FindControl(Tag:="__myPlyMacs__")
If ctrl Is Nothing Then
Err.Clear
Exit Do
End If
ctrl.Delete
Loop
On Error GoTo 0

End Sub

Ivan wrote:
>
> Dear all,
>
> I want to write a macro program so that when right click at on the
> worksheet, an item is added to the right-click menu with the name of macro
> function, so that the users can call the macro program by right clicking the
> worksheet. On the other hand, other regular items such as "Copy",
> "Paste",..,etc., should be preserved on the right-click menu.
> Can anyone advise how to do this programatically? Thanks in advance!
>
> Ivan

--

Dave Peterson

Thanks a lot Dave!

"Dave Peterson" wrote:

> One way:
>
> Option Explicit
> Sub auto_close()
> Call CleanUpPly
> End Sub
> Sub auto_open()
>
> Dim iCtr As Long
> Dim myMacros As Variant
> Dim myCaptions As Variant
>
> Call CleanUpPly
>
> Dim cb As CommandBar
> Set cb = Application.CommandBars("ply")
>
> myMacros = Array("mac1", "mac2", "mac3")
> myCaptions = Array("Cap 1", "Cap 2", "Cap 3")
>
> With cb.Controls
> For iCtr = LBound(myMacros) To UBound(myMacros)
> With .Add(Type:=msoControlButton, temporary:=True)
> .Caption = myCaptions(iCtr)
> .OnAction = "'" & ThisWorkbook.Name & "'!" & myMacros(iCtr)
> .Tag = "__myPlyMacs__"
> End With
> Next iCtr
> End With
>
> End Sub
> Sub CleanUpPly()
> Dim ctrl As CommandBarControl
>
> On Error Resume Next
> Do
> Set ctrl = Application.CommandBars("Ply") _
> .FindControl(Tag:="__myPlyMacs__")
> If ctrl Is Nothing Then
> Err.Clear
> Exit Do
> End If
> ctrl.Delete
> Loop
> On Error GoTo 0
>
> End Sub
>
> Ivan wrote:
> >
> > Dear all,
> >
> > I want to write a macro program so that when right click at on the
> > worksheet, an item is added to the right-click menu with the name of macro
> > function, so that the users can call the macro program by right clicking the
> > worksheet. On the other hand, other regular items such as "Copy",
> > "Paste",..,etc., should be preserved on the right-click menu.
> > Can anyone advise how to do this programatically? Thanks in advance!
> >
> > Ivan
>
> --
>
> Dave Peterson
>