#1
|
|||
|
|||
Add item to context menu for a specific workbook
I want to add an item to the context (right-click) menu for a specific workbook. The code below does what I want, except that it adds the item for all workbook menus. Is there some way to limit the scope to a single workbook?
Sub AddItemShortCutMenu() Dim Shortcut As CommandBar Dim NewItem As CommandBarButton Set Shortcut = Application.CommandBars("Cell") Set NewItem = Shortcut.Controls.Add(Type:=msoControlButton) With NewItem .Caption = "Custom Action" .OnAction = "CustomSubroutine" 'The subroutine must be located in Module1 for some reason End With End Sub |
#2
|
|||
|
|||
I found a work-around: Put
Sub Auto_Close() Application.CommandBars("Cell").Reset End Sub in Module1 to reset the menu when the workbook is closed. |
#3
|
|||
|
|||
Also ... for those users who might have more than one workbook open/running at the same time, you can add to the "Set Shortcut" code line ...
Code:
Sub AddItemShortCutMenu() Dim Shortcut As CommandBar Dim NewItem As CommandBarButton Set Shortcut = ThisWorkbook.Application.CommandBars("Cell") Set NewItem = Shortcut.Controls.Add(Type:=msoControlButton) With NewItem .Caption = "Custom Action" .OnAction = "CustomSubroutine" 'The subroutine must be located in Module1 for some reason End With End Sub |
#4
|
|||
|
|||
That's better - a solution, not just a work-around. Thanks!
|
#5
|
|||
|
|||
Whoops, perhaps not. I tried changing the line to
Set Shortcut = ThisWorkbook.Application.CommandBars("Cell") as you suggested. However when I opened a new blank workbook, the context item was still there. And if I shut down the workbook where I ran the code without invoking the Auto_Close subroutine the context item still persisted in a new blank workbook. The "ThisWorkbook" limiter doesn't seem to work, at least in my case. |
#6
|
|||
|
|||
Hmmm .. I don't know what to say. It works here.
??? Late Edit: Ooops ... no it does not. It did when I posted the answer earlier ... but now it is not. Curious. Let me look into this some more. |
#7
|
|||
|
|||
.
Ok ... did a little research. Try this version. Paste both in the THISWORKBOOK module. Code:
Option Explicit 'Sub Auto_Close() 'Application.CommandBars("Cell").Reset 'End Sub Private Sub Workbook_Deactivate() On Error Resume Next With Application .CommandBars("Cell").Controls("My Macro").Delete End With On Error GoTo 0 End Sub Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim cBut As CommandBarButton On Error Resume Next With Application .CommandBars("Cell").Controls("My Macro").Delete Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True) End With With cBut .Caption = "My Macro" .Style = msoButtonCaption .OnAction = "My_Macro" End With On Error GoTo 0 End Sub |
#8
|
|||
|
|||
Yes, that works - thanks!
|
#9
|
|||
|
|||
You are welcome. Glad to assist !
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Context Menu Unavailable | Project Pro | Team Planner View | JKIT | Project | 0 | 12-14-2020 11:28 AM |
Remove new macros menu item | aufet | Word VBA | 1 | 11-08-2019 05:44 PM |
Powerpoint 2016 RC context menu after update | AdamC | PowerPoint | 0 | 06-26-2017 11:12 AM |
Modify right-click context menu to only allow pasting text | derajlance | Word VBA | 0 | 05-24-2016 02:25 PM |
Call for votes: office 2013 Autocorrect & right click (context menu) | franklekens | Word | 0 | 09-20-2013 12:09 AM |