Deactivate some of the options on the right click of the mouse button in Excel

Question

I'm trying to deactivate some of the options on the right click of the mouse button in Excel.

A lot of them work but "New Comment", "New Note" and "Format Cells..." don't with the format: Application.CommandBars("cell").Controls("New Comment").Enabled = False.

I'm looking for the syntax to get these commands but also is there a way to do the same thing using the control ID (which for "New Comment" is, I think, ID01589. Finding the ID for a lot of these right click options seems to be easier then using the label.

In addition to finding the syntax for the above three items on the right click menu I would like to know how to do it for items in other menus i.e. teach me how to fish rather than giving me a fish.

Answer 1

My knowledge here comes from repeated trial and error over a number of projects over a number of years. Hence I do not claim definitive knowledge. However, this process, if followed, will get you your fish and I will teach you how to fish as best as I know it!

There are two separate approaches required for this ... 'menu items' are not all added in the same way. I believe Microsoft changed how they added when they introduced the 'ribbon' and RibbonX. First, Format Cells which is added the 'old way':

To get the IDs for the menu items (CommandBarControls) on the 'cell' menu, you can use this:

Sub ListCommandBarControlsForCellMenu()
    Dim cb As CommandBar, cbc As commandBarControl
    Set cb = Application.CommandBars("Cell")
    For Each cbc In cb.Controls
        Debug.Print Now, cbc.ID, "'" & cbc.Caption & "'"
    Next cbc
End Sub

From this you will see the ID for 'Format Cells' is 855 and so you can disable it using Application.CommandBars("Cell").FindControl(ID:=855, Recursive:=True).Enabled = False (and obviously True to enable it).

Next, New Note and New Comment which are added the 'new way':

These are added using RibbonX and you will need a RibbonX editor app to assist you with editing it ... there are a few RibbonX editors out there, I suggest Office RibbonX Editor (I have no affiliation with the author), you will need to read-up on how to use this. Once you have got the hang of how to add/edit RibbonX, you need to know that the idMso for New Comment is NewThreadedComment and the idMso for New Note is NewCommentLegacy and so the RibbonX code you will need is:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <contextMenus>
        <contextMenu idMso="ContextMenuCell">
            <button idMso="NewCommentLegacy" 
                enabled="false" />
            <button idMso="NewThreadedComment" 
                enabled="false" />
        </contextMenu>
    </contextMenus>
</customUI>

The process for working out the idMso values is not straight forward. However, the approach I used here (and have used before ... sometimes successfully and sometimes not) is: in Excel, go to: File > Options > Customize Ribbon, under 'Choose Commands From' select 'All Commands' ... at this point, then, you have to work out which command in the (very long) drop-down relates to the 'menu items' you want.

In your case, scroll to Insert Comment (there are two on my device, it is the first) and hover the mouse over it, you will see NewThreadedComment in parenthesis at the end of the tooltip ... this is the idMso for New Comment (why Microsoft uses Insert Comment in one place and New Comment in another I do not know ... part of the 'fun and games' of this is to work out what Microsoft called each item, there is no definitive way of doing this that I know of, you need to hunt around in the drop-down until you find the command you need); then scroll to New Note and again hover your mouse to find the idMso is NewCommentLegacy. These are then used in the RibbonX, as above, to disable the relevant menu items.

To read up more about RibbonX, including how to make the disabling/enabling dynamic (if that's what you need) using getEnabled, see the Microsoft docs.

Answer 2

For most of the cell controls I recommend enumerating the Cell MenuItem ids and a function to return the control.
Note these controls are hidden and cannot be unhidden:

cmInsertComment = 2031

cmDeleteComment = 1592
cmShowHideComments = 1593

As JohnM pointed out you'll need to edit the Ribbon to disable the menu items. I used Custom UI Editor to insert xml to disable the New Comment and New Note MenuItems.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <commands>
    <command idMso="NewThreadedComment" enabled="false"/>
    <command idMso="NewCommentLegacy" enabled="false"/>
  </commands>
</customUI>

Public Enum CellMenuControlID
    cmCut = 21
    cmCopy = 19
    cmPaste = 22
    cmPasteSpecial = 21437
    cmPasteTable = 3624
    cmSmartLookup = 25536
    cmPictureInCell = 34738
    cmShowDataTypeCard = 32714
    cmDataType = 32713
    cmViewAltText = 34737
    cmInsert = 3181
    cmDelete = 292
    cmClearContents = 3125
    cmTranslate = 33409
    cmQuickAnalysis = 24508
    cmSparklines = 31623
    cmFilter = 31402
    cmOutputAs = 34750
    cmSort = 31435
    cmGetDataFromTableRange = 34003
    cmInsertComment = 2031
    cmDeleteComment = 1592
    cmShowHideComments = 1593
    cmFormatCells = 855
    cmPickFromDropdownList = 1966
    cmShowPhoneticField = 1614
    cmDefineName = 13380
    cmOpenWith = 34600
    cmSetOpenDefault = 34680
    cmHyperlink = 1576
    cmEditHyperlink = 1577
    cmOpenHyperlink = 1015
    cmRemoveHyperlink = 3626
    cmLinkToThisRange = 34646
    cmShowChanges = 34405
    cmShowDiagnostics = 34713
    cmExpandToDetail = 11299
    cmAdditionalActions = 31595
    cmFullScreen = 178
    cmHideOthers = 34125
    cmAdditionalActions2 = 22577
    cmPeopleNearMe = 34042
    cmShowVBEditor = 1
End Enum

Function getCellMenuControl(Id As CellMenuControlID) As CommandBarControl
    Set getCellMenuControl = Application.CommandBars("Cell").FindControl(Id:=Id)
End Function