Call Macro using right click menu
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
|