Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-24-2020, 10:42 AM
coffent coffent is offline Add item to context menu for a specific workbook Windows 10 Add item to context menu for a specific workbook Office 2019
Advanced Beginner
Add item to context menu for a specific workbook
 
Join Date: Dec 2019
Posts: 40
coffent is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 12-24-2020, 11:51 AM
coffent coffent is offline Add item to context menu for a specific workbook Windows 10 Add item to context menu for a specific workbook Office 2019
Advanced Beginner
Add item to context menu for a specific workbook
 
Join Date: Dec 2019
Posts: 40
coffent is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 12-24-2020, 12:15 PM
Logit Logit is offline Add item to context menu for a specific workbook Windows 10 Add item to context menu for a specific workbook Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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
Reply With Quote
  #4  
Old 12-24-2020, 07:38 PM
coffent coffent is offline Add item to context menu for a specific workbook Windows 10 Add item to context menu for a specific workbook Office 2019
Advanced Beginner
Add item to context menu for a specific workbook
 
Join Date: Dec 2019
Posts: 40
coffent is on a distinguished road
Default

That's better - a solution, not just a work-around. Thanks!
Reply With Quote
  #5  
Old 12-24-2020, 07:59 PM
coffent coffent is offline Add item to context menu for a specific workbook Windows 10 Add item to context menu for a specific workbook Office 2019
Advanced Beginner
Add item to context menu for a specific workbook
 
Join Date: Dec 2019
Posts: 40
coffent is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 12-24-2020, 08:41 PM
Logit Logit is offline Add item to context menu for a specific workbook Windows 10 Add item to context menu for a specific workbook Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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.
Reply With Quote
  #7  
Old 12-24-2020, 08:54 PM
Logit Logit is offline Add item to context menu for a specific workbook Windows 10 Add item to context menu for a specific workbook Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
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
Reply With Quote
  #8  
Old 12-25-2020, 09:26 AM
coffent coffent is offline Add item to context menu for a specific workbook Windows 10 Add item to context menu for a specific workbook Office 2019
Advanced Beginner
Add item to context menu for a specific workbook
 
Join Date: Dec 2019
Posts: 40
coffent is on a distinguished road
Default

Yes, that works - thanks!
Reply With Quote
  #9  
Old 12-25-2020, 09:33 AM
Logit Logit is offline Add item to context menu for a specific workbook Windows 10 Add item to context menu for a specific workbook Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

You are welcome. Glad to assist !
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:40 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft