How to Disable/Enable any worksheet's selected right click menu items

Using VBA I need to disable/enable worksheet menu items that are displayed via right click on a tab.  Trying to disable/enable all menu items except "View Code", "Protect Sheet..." and "Select All Sheets".  I do not want to completely disable the right click function on a tab which shows nothing after right click. I also do not want to disable the right click option on any cell on the worksheet.  Only the select menu items on a tab should be disabled as described above.  Attached file is blank with only the same question displayed on it.
TabMenu.xlsx
Ray ErdenBusiness Systems AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
I don't think that's possible. I believe you can intercept the right-click on a cell and change the context menu, but I don't think you can do that for the tab.
0
Dale FyeOwner, Developing Solutions LLCCommented:
The first key is figuring out which shortcut menu you need to modify.

Try putting the following in a code module:

Public Sub WhatsThis()

   Dim cbr As CommandBar
   Dim cbtn As CommandBarButton
   
   On Error GoTo ProcError
   
   For Each cbr In Application.CommandBars
   
        With cbr
    
            Set cbtn = cbr.Controls.Add(1, , , , True)
            cbtn.BeginGroup = true
            cbtn.Caption = "What's This"
            cbtn.OnAction = "WhatsThisBar"
        
        End With
        
NextCBR:
    Next
   
ProcExit:
    Exit Sub
    
ProcError:
    'Debug.Print cbr.Name, Err.Number, Err.Description
    Resume NextCBR
    
End Sub

Open in new window

When you run this code, it will add an option "What's This" to the bottom of most of the commandbar menus.  When you click on that shortcut option, it will print the name of the commandbar in the immediate window using the following code:
Public Sub WhatsThisBar()

    Debug.Print CommandBars.ActionControl.Parent.Name
    
End Sub

Open in new window

You can then run the following code to identify all of the controls listed in a particular commandbar.  Syntax would be:
BarControls "Column"
Public Sub BarControls(BarName As String)

    Dim cbr As CommandBar
    Dim ctrl As CommandBarControl
    
    Set cbr = Application.CommandBars(BarName)
    For Each ctrl In cbr.Controls
        Debug.Print ctrl.Caption, IIf(IsNull(ctrl.ID), "NA", ctrl.ID)
    Next
    
End Sub

Open in new window

This will print a list of the control captions used in the commandbar and the ID values associated with each of those controls.  To make a control hidden or visible, you could use code similar to:
Public Sub ShortcutControlVisible(BarName as string, _
          CtrlCaption as string, Optional IsVisible as boolean = True)

    Commandbars(BarName).Controls(CtrlCaption).Visible = IsVisible

End Sub

Open in new window

In order for this to work properly, you would call it like:
ShortcutControlVisible "Column", "Cu&t", False
ShortcutControlVisible "Column", "Cu&t"
0
Martin LissOlder than dirtCommented:
Wait. I may have found a way. Be back soon.
0
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Dale FyeOwner, Developing Solutions LLCCommented:
Martin,

Seems to work for me.  The CommandBar that is selected when rightclicking on the worksheet tab is "Ply", and I was able to hide the several controls I attempted to using the code above.

The problem is that when you start playing with system commandbars, you need to make sure that you change those values back before you close EXCEL, or they will retain those values.  For example,  when I set the visible property of the "&Insert..." and "&Delete" controls on the "Ply" commandbar, and then closed Excel, it retained those as Visible = False when I opened Excel the next time.

I'm not familiar enough with the Excel Object Model, but I'm sure there must be some form of BeforeClose event that would allow you to call your code and restore those values before Excel is closed.
0
Ray ErdenBusiness Systems AnalystAuthor Commented:
Dale,
I have followed your instructions and entered the code provided above, ran it and got the command bar name as Ply in the immediate window but I am not close to solution.  I could not get the menu items listed to be deactivated.  Not sure what I missed in the process but couldn't get it work.  Attached file is modified per the recommended code.
TabMenu.xlsm
0
Martin LissOlder than dirtCommented:
I tried things like this and couldn't get it to work either.

Application.CommandBars("Ply").Controls("Rename").Enabled = False
0
Dale FyeOwner, Developing Solutions LLCCommented:
As indicated above, you must use the EXACT string text that gets printed out for each caption, including the "&" and any "...".

Application.CommandBars("Ply").Controls("&Rename").Enabled = False

You can also use syntax:

Application.CommandBars("Ply").Controls(Index#).Enabled = False

But I prefer to use the Caption, so that I know precisely which control is being enabled/disabled.
0
Martin LissOlder than dirtCommented:
Application.CommandBars("Ply").Controls("&Rename").Enabled = False

doesn't work either (in Excel 2010)
0
Martin LissOlder than dirtCommented:
Application.CommandBars("Ply").Controls("Rename").Enabled = False

Does work in Excel 2007, so I'm assuming that in 2010 that you have to do something via the ribbon.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Sorry, guys, I've been working on my 2007 machine and missed the reference to 2010.  Let me pull out my laptop and take a look at 2010.
0
Martin LissOlder than dirtCommented:
This thread probably has the answer but I'm sorry but I don't want to get into it and try it.
0
Dale FyeOwner, Developing Solutions LLCCommented:
I don't have 32 bit 2010, but I do have 64 bit 2010.

Interesting, when I do this:

Commandbars("Ply").controls(1).Enabled = false

 in the immediate window and then use:

Commandbars("Ply").showpopup

setting the controls enabled property works, but when you actually right click on the tab, it doesn't, so the context menu of the tab must be automatically resetting the enabled property to true.  Also, when I set the Visible property to False, and use the above line, it does not display the Insert control, but when I right click on the tab control, that option is visible again.

You can actually disable the right click with:

Commandbars("Ply").Enabled = False

In Access, I occasionally disable the default right click menus this way, and then use the MouseUp event associated with forms and controls to popup my own shortcut menus, but I'm not familiar enough with the Excel Object model to know where there is a trappable event associated with the tab control.

I don't see anything that jumps out to me when I select the ThisWorkbook in the project window and then select "Workbook" in the VBE dropdown window.
0
Martin LissOlder than dirtCommented:
In my research for this question I was referred to the thread I posted by another thread which said
in 2010 the worksheet tab context menu is now a Ribbon control. The old "ply" commandbar still exists but is not visble and serves no purpose.
0
Ray ErdenBusiness Systems AnalystAuthor Commented:
Dale - After testing on your 2010 laptop could you please send me the working copy of the file that I sent to see how that code works? For example for Delete option?  I tried "Application.CommandBars("Ply").Controls("Rename").Enabled = False" yet failed again.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Oh, well!  Thanks, Martin.  Good info to know.

Interesting though that when I did:

Commandbars("Ply").Enabled = False

The shortcut did not display.
0
Martin LissOlder than dirtCommented:
The shortcut did not display.
If that's the case then along with that would supplying your users with buttons to do the "View Code", "Protect Sheet..." and "Select All Sheets" actions work for you?
0
Ray ErdenBusiness Systems AnalystAuthor Commented:
Actually those options that you listed better stay in the right click menu of the tab as active or enabled while the rest of the menu options being disabled.  I prefer not to supply separate buttons for those options.
0
Ray ErdenBusiness Systems AnalystAuthor Commented:
Dale -  Think you can send me the copy of the file with the working solution when you can?  Thanks.
0
Dale FyeOwner, Developing Solutions LLCCommented:
trusxlsol,

There does not appear to be a working solution which will make just those 3 items visible and enabled in Office 2010, unless you use the solution mentioned by Martin.
0
Ray ErdenBusiness Systems AnalystAuthor Commented:
Could you then send me the working copy of your solution as 2007 file since you got it worked on Excel 2007?
0
Martin LissOlder than dirtCommented:
I'm going to teach myself how to modify the ribbon XML. I'll let you know one way or the other if I can then do what you need so please wait a couple of days before closing this question if you want a 2010 solution.
0
Martin LissOlder than dirtCommented:
Based on Ron deBruin's article and this XML code

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false" /> 
<contextMenus> 
<contextMenu idMso="ContextMenuWorkbookPly"> 
<button enabled="false" idMso="SheetDelete" label="Delete..." showLabel="true" /> 
<button enabled="false" idMso="SheetInsertPage" label="Insert..." showLabel="true" />
<button enabled="false" idMso="SheetRename" label="Rename" showLabel="true" />  
<button enabled="false" idMso="SheetMoveOrCopy" label="Move or Copy..." showLabel="true" />  
<gallery enabled="false" idMso="SheetTabColorGallery" label="Tab Color" showLabel="true" /> 
<gallery enabled="false" idMso="SheetTabColorMoreColorsDialog" label="More Colors..." showLabel="true" /> 
<button enabled="false" idMso="SheetHide" label="Hide" showLabel="true" /> 
<button enabled="false" idMso="SheetUnHide" label="Unhide..." showLabel="true" /> 
</contextMenu> 
</contextMenus> 
</customUI>

Open in new window

I was able to do everything you wanted except being able to disable the 'Tab Color|More Colors' context menu item. I suspect the problem is that 'More Colors' is a sub-item to 'Tab Colors' and I need to express that relationship in the XML. I'm still trying to figure out how to do that.
Ribbon.xlsm
0
Martin LissOlder than dirtCommented:
Success! I worked around the problem by hiding rather than disabling the unwanted items with this code. (Note that in the previous code the idMso="SheetUnHide" should have been idMso="SheetUnhide" like it is here.)

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false" /> 
<contextMenus> 
<contextMenu idMso="ContextMenuWorkbookPly"> 
<button visible="false" enabled="false" idMso="SheetDelete" label="Delete..." showLabel="true" /> 
<button visible="false" enabled="false" idMso="SheetInsertPage" label="Insert..." showLabel="true" />
<button visible="false" enabled="false" idMso="SheetRename" label="Rename" showLabel="true" />  
<button visible="false" enabled="false" idMso="SheetMoveOrCopy" label="Move or Copy..." showLabel="true" />  
<gallery visible="false" enabled="false" idMso="SheetTabColorGallery" label="Tab Color" showLabel="true" /> 
<button visible="false" enabled="false" idMso="SheetHide" label="Hide" showLabel="true" /> 
<button visible="false" enabled="false" idMso="SheetUnhide" label="Unhide..." showLabel="true" /> 
</contextMenu> 
</contextMenus> 
</customUI> 

Open in new window

Ribbon.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
Nice detective work, Martin.
0
Martin LissOlder than dirtCommented:
Thanks.
0
Ray ErdenBusiness Systems AnalystAuthor Commented:
You bet it is success Martin!  Thank you for your effort vested in this problem, it works just great!  Dale I appreciate your help as well.
0
Ray ErdenBusiness Systems AnalystAuthor Commented:
Super!
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
Ray ErdenBusiness Systems AnalystAuthor Commented:
I will certainly review those articles...thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.