Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call Macro using right click menu
Why not just add a command button with the macro assigned ---------------- Ben -- protonLeah ------------------------------------------------------------------------ protonLeah's Profile: http://www.excelforum.com/member.php...o&userid=32097 View this thread: http://www.excelforum.com/showthread...hreadid=538298 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call Macro using right click menu
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I add a Macro to right-click Menu? | Excel Discussion (Misc queries) | |||
How to call a sub defined in macro when double click a cell? | Excel Programming | |||
Assign macro to right click menu ? | Excel Programming | |||
Call Userfor and click button with macro | Excel Programming | |||
Macro doesn't perform correctly when using right click menu | Excel Programming |