Add item to context menu for a specific workbook

[coffent]

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]

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]

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 ...

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]

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

[coffent]

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]

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]

.
Ok ... did a little research. Try this version.
Paste both in the THISWORKBOOK module.

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]

Yes, that works - thanks!

[Logit]

You are welcome. Glad to assist !