Call Macro using right click menu

[Guest]

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

 

[protonLeah]

Why not just add a command button with the macro assigned

 

[Dave Peterson]

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

 

[Guest]

Thanks a lot Dave!

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