Add item to context menu for a specific workbook

coffent · #1 12-24-2020, 10:42 AM

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

coffent · #2 12-24-2020, 11:51 AM

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.

Logit · #3 12-24-2020, 12:15 PM

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

coffent · #4 12-24-2020, 07:38 PM

That's better - a solution, not just a work-around. Thanks!

coffent · #5 12-24-2020, 07:59 PM

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.

Logit · #6 12-24-2020, 08:41 PM

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.

Logit · #7 12-24-2020, 08:54 PM

.
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

coffent · #8 12-25-2020, 09:26 AM

Yes, that works - thanks!

Logit · #9 12-25-2020, 09:33 AM

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