From time to time you may need to prevent users from Deleting or Inserting rows or columns on an Excel worksheet. Further, it would also be nice to block any unnecessary Sorting and Filtering. Excel’s context menus (right click popup menu) give the user access to many functions based on the currently selected portion of the worksheet. This access can be a nightmare for a programmer since there is no way to trap and stop these actions in Excel’s VBA lexicon.

I know that protecting the worksheets can help and I use it, but there are times when the worksheets need to be unprotected to allow the user (or a SuperUser) to do some work. This state of being unprotected makes available the effortless context menu options of DELETE, INSERT, SORT, FILTER, HIDE, UNHIDE and more. This makes it too easy for the user to “tamper” with the workbook and thus is a risky situation.

How can you disable (and re-enable) these menu items ?

This post will explain how simple it is, using Visual Basic for Applications (VBA), to dynamically and at specific times in the processing of data to modify the context menus.

There are 2 commands which will do the job:

Application.CommandBars("Cell").Controls("Clear Co&ntents").Enabled = False (or True)
Application.CommandBars.FindControl(ID:= 3125).Enabled = False (or True)

However, they differ in both their dependability and accuracy. The first command selects the menu item to enable by first naming the context menu, “Cell” and then selecting a control from that menu using the “label” text, “Clear Co&ntents.” The second argument, “Clear Co&ntents” is what is entered to show the menu item “Clear Contents”. If you are familiar with Windows interface programming, you know that the & comes before the “keyboard accelerator” letter. In this case “n”.

The second command references the menu, “Row”, but uses a more stable and accurate reference, the control id. Why is this a better command? The control id indexes a specific control and acts only on controls with that id. The first command relies on the text which you see on the menu. This can be misleading. For example, the command will work on the “Delete” menu item, by using “&Delete”, or “&Delete…”, but will also be prone to throwing an error saying that an argument is bad.

So, how do you find the Control ID?

There are spreadsheets available with all the Control IDs in every Microsoft Office product from which you can find what you need. The best way I have found to get a correct Control ID for a specific context menu item is by using a free program from the Add-in Express website, called the “Built-in Controls Scanner.” The Built-in Controls Scanner to displays command bar names and Control IDs in an easy to read hierarchical listing. You can get a copy of it here.

In the following image of the Built-in Controls Scanner in use you can see a listing for the “Host Application”, Excel and of the “Cell” context menu items. The red arrow points to the Delete command, listed as “&Delete…” with the “Office ID” of 292.

Using this information, you can complete the command to disable the Delete menu item on the context menu which appears when you right click on a cell like this:

Application.CommandBars("Cell").Controls("&Delete…").Enabled = False

Unfortunately, running this code triggered the runtime error 5 , “Invalid procedure call or argument”. If you go back to the Built-in Controls Scanner and look at the Delete command for the Row and Column menus (the context menus opened when you have selected a row or column) you will see a difference. While the text for the menu item is identical, the Control IDs are different. This is the issue, when you depend on the menu text you may be trying to reference different controls with one command. This doesn’t work consistently and reliably. We know that the Cell menu argument is correct, but if we change the “&Delete…” to “Delete…” and run it again we get:

A much better and more predicable solution is…

Application.CommandBars.FindControl(ID:=292).Enabled = False

Use the FindControl method to change the menus with confidence and reliability. While it can be used as a single command or two, changing many controls can get very verbose. I have found for my needs that For Each loops work well. The final code example is how I implemented the menu changes using loops with minimal code. Note that the array is filled from a well commented listing which makes updating the code a breeze. I have to admit that this code was “tuned up” by using the suggestions from this post on StackOverflow.

Public Sub Set_Menus(Optional CloseUp As Boolean = False)
' Set the visibility of some context menu items to prevent issues
' Use the app BuiltInControlsScanner.exe for ids (in /etc)
' Sheet1.Range("T16") saves the value of the workbook protected or not

Dim ctrl As Office.CommandBarControl
Dim arrIdx(13) As Long
Dim idx As Variant
Dim bolShowMnu As Boolean
Dim mnuControls As CommandBarControls

    ' Assign values to each element of the array - arrIdx, this can be added to as needed ;)
    arrIdx(0) = 292         ' Cell Delete
    arrIdx(1) = 293         ' Row Delete
    arrIdx(2) = 294         ' Column Delete
    arrIdx(3) = 295         ' Cell Insert
    arrIdx(4) = 27960       ' Row & Column Insert
    arrIdx(5) = 3125        ' Clear Contents
    arrIdx(6) = 31402       ' Cell Filter
    arrIdx(7) = 31435       ' Cell Sort
    arrIdx(8) = 541         ' Row Height
    arrIdx(9) = 542         ' Column Height
    arrIdx(10) = 883        ' Row Hide
    arrIdx(11) = 884        ' Row Unhide
    arrIdx(12) = 886        ' Column Hide
    arrIdx(13) = 887        ' Column Unhide

    If Sheet1.Range("T16").value = True Or CloseUp = True Then
    ' If the workbook is protected or we want to close it, set them back to Visible...
        bolShowMnu = True
    Else ' Hide the menu items
        bolShowMnu = False
    End If
    
    ' If the workbook is protected or we want to close it, set them back to Visible...
    For Each idx In arrIdx
        Set mnuControls = Application.CommandBars.FindControls(ID:=idx)
        If Not mnuControls Is Nothing Then 'If no CommandBarControls were found skip the following
            For Each ctrl In mnuControls
                ctrl.Enabled = bolShowMnu
            Next ctrl
        End If
    Next idx

End Sub

A final note…

Beware that these commands change Excel’s menus application wide and permanently. All new workbooks will have the context menu items disabled per your code. You need to allow for resetting this if you want to have the original functionality later. To do that you simply change the False to True and run the command again. You could use the Workbook_Open and the Workbook_BeforeClose events to trigger the changes, setting things back to normal on the close. However, using the Worksheet_Activate and Worksheet_Deactivate events are suitable as well. As long as you remember to set things right when to workbook closes!

Further reading…

Depending on your abilities and desire for more information may also like to read this fine post which also covers this topic:

https://www.rondebruin.nl/win/s6/win001.htm