Hiding Command Bar Right Click Options

Question

I am trying to hide most of the application.Commandbar options when a user right clicks.

Usually it works but sometimes I get a debug error.

I originally managed to get it working with the below:

Private Sub RightClick()

Application.CommandBars("Cell").Reset
Application.CommandBars("Cell").Enabled = True

Application.CommandBars("Cell").Controls("paste").Delete
Application.CommandBars("Cell").Controls("Paste &Special...").Delete
Application.CommandBars("Cell").Controls("Cu&t").Delete
Application.CommandBars("Cell").Controls("Smart &Lookup").Delete
Application.CommandBars("Cell").Controls("Insert").Delete
Application.CommandBars("Cell").Controls("Delete...").Delete
Application.CommandBars("Cell").Controls("&Copy").Delete
Application.CommandBars("Cell").Controls("Filt&er").Delete
Application.CommandBars("Cell").Controls("S&ort").Delete
Application.CommandBars("Cell").Controls("&Get Data from Table/Range...").Delete
Application.CommandBars("Cell").Controls("&Format Cells...").Delete
Application.CommandBars("Cell").Controls("Pic&k From Drop-down List...").Delete
Application.CommandBars("Cell").Controls("Define N&ame...").Delete
Application.CommandBars("Cell").Controls("&Quick Analysis").Delete
Application.CommandBars("Cell").Controls("Clear Co&ntents").Delete
Application.CommandBars("Cell").Controls("&Hyperlink...").Delete
Application.CommandBars("Cell").Controls("Translate").Delete

Application.ShowMenuFloaties = True
Application.CutCopyMode = False

End Sub

I have debugged and the error seems to come up due to these two lines:

Application.CommandBars("Cell").Controls("Insert").Delete
Application.CommandBars("Cell").Controls("Delete...").Delete

I have read online that its best to use the ID instead which I have managed to get:

CODE
21 Cu&t
19 &Copy
22 &Paste
21437 Paste &Special...
3624 &Paste Table
25536 Smart &Lookup
32714 &Show Data Type Card
32713 Data T&ype
295 Insert C&ells...
292 &Delete...
3125 Clear Co&ntents
33409 Translate
24508 &Quick Analysis
31623 Sp&arklines
31402 Filt&er
31435 S&ort
34003 &Get Data from Table/Range...
2031 Insert Co&mment
1592 Delete Co&mment
1593 Sh&ow/Hide Comments
855 &Format Cells...
1966 Pic&k From Drop-down List...
1614 &Show Phonetic Field
13380 Define N&ame...
1576 &Hyperlink...
1577 Edit &Hyperlink...
1015 &Open Hyperlink
3626 &Remove Hyperlink
34405 Show Chan&ges
11299 E&xpand to detail
31595 Additional Act&ions
178 F&ull Screen
34125 Hide Ot&hers
22577 &Additional Actions
34042 People &Near Me

Essentially I need only the "Add Comment" option to come up when a user right clicks but I cant work out how to use the ID instead of the name.

This doesn't work.

Application.CommandBars("Cell").Controls(ID = "21").Delete

Answer 1

Deleting the Cell menu item Delete work correctly.. But it will throw an error is the control has been deleted already.

Application.CommandBars("Cell").Controls("Delete...").Delete

This will not work because the caption is incorrect:

Application.CommandBars("Cell").Controls("Insert").Delete

Use

Application.CommandBars("Cell").Controls("Insert...").Delete

I recommend using a separating subroutine to delete the control. this will make it easier Debug the code.

Sub HideCellMenuItems()
    Application.CommandBars("Cell").Reset
    Dim CommandNames() As Variant
    CommandNames = Array("paste", "Paste &Special...", "Cu&t", "Smart &Lookup", "Insert...", "Delete...", "&Copy", "Filt&er", "S&ort", "&Get Data from Table/Range...", "&Format Cells...", "Pic&k From Drop-down List...", "Define N&ame...", "&Quick Analysis", "Clear Co&ntents", "&Hyperlink...", "Translate")

    Dim CommandName As Variant
    For Each CommandName In CommandNames
        HideCellMenuItem CommandName
    Next
End Sub


Sub HideCellMenuItem(CommandName As Variant)
    Dim Item As CommandBarControl
    On Error Resume Next
    Set Item = Application.CommandBars("Cell").Controls(CommandName)
    If Err.Number = 0 Then
        Item.Delete
    Else
        Debug.Print CommandName; " not found"
    End If
    On Error GoTo 0
End Sub

Addendum

Here is an alternate approach where we hide all the items but the ones in an array.

Sub HideCellMenuItems()
    Dim NamesToKeep As Variant
    NamesToKeep = Array("Insert Co&mment", "Delete Co&mment")
    Dim Control As CommandBarControl
    Application.CommandBars("Cell").Reset
    For Each Control In Application.CommandBars("Cell").Controls
        If Not IsInArray(Control.Caption, NamesToKeep) Then
            Control.Delete
        End If
    Next Control
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Answer 2

The ID isn't a string ... also, you do not have to delete the relevant items, you can just hide them (ie make them non-visible), eg

Application.CommandBars("Cell").FindControl(ID:=21, Recursive:=True).Visible = False

EDIT FOR USER'S COMMENT

A better solution is like this

Sub ShowHideControls(sCommandBarName As String, bVisible As Boolean)
    Dim c As Office.CommandBarControl
    For Each c In Application.CommandBars(sCommandBarName).Controls
        c.Visible = bVisible
    Next c
End Sub

Which you can call like this:

ShowHideControls "Cell", False

And it will hide all of the items (swap False for True to show all of the items again) other than those added via RibbonX ... for which see this SO answer

Answer 3

Command bars were deprecated. Starting Excel 2007 the Fluent UI (aka Ribbon UI) must be used for any UI customizations. The CommandBar-related classes are left for executing controls programmatically, for example.