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

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.

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

Select allOpen 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

Select allOpen 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

Select allOpen 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

Select allOpen in new window

In order for this to work properly, you would call it like:
ShortcutControlVisible "Column", "Cu&t", False
ShortcutControlVisible "Column", "Cu&t"

Wait. I may have found a way. Be back soon.

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.

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

I tried things like this and couldn't get it to work either.

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

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.

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

doesn't work either (in Excel 2010)

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.

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.

This thread probably has the answer but I'm sorry but I don't want to get into it and try it.

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.

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.

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.

Oh, well!  Thanks, Martin.  Good info to know.

Interesting though that when I did:

Commandbars("Ply").Enabled = False

The shortcut did not display.

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?

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.

Dale -  Think you can send me the copy of the file with the working solution when you can?  Thanks.

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.

Could you then send me the working copy of your solution as 2007 file since you got it worked on Excel 2007?

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.

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>

Select allOpen 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

Nice detective work, Martin.

Thanks.

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.

Super!

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

I will certainly review those articles...thanks.