Programming Excel with VBA and .NET

Change Context Menu Items

Just as with other menus, you can modify items on context menus by getting a reference to the item using the FindControl method. The following code modifies the Paste item on the Cell menu to paste the contents as text, which removes any formatting from the source:

Sub ChangeCellMenuItem( )
    Dim cb As CommandBar, cbtn As CommandBarButton
    Dim index As Integer
    ' Get the cell context menu.
    Set cb = Application.CommandBars("Cell")
    ' Get the Paste menu item (ID is 22).
    Set cbtn = cb.FindControl(msoControlButton, 22)
    ' Replace the action.
    cbtn.OnAction = "PasteAsText"
End Sub
 
' Procedure used by OnAction property.
Sub PasteAsText( )
    ActiveSheet.PasteSpecial "Text"
End Sub

As I mentioned earlier, finding the control ID for menu items can be tricky. The following code displays a list of the context menu names, the items they contain, and the control IDs for each of the items:

Sub ListContextMenus( )
    Dim cb As CommandBar, cbtn As CommandBarButton
    Debug.Print "Context menus", ""
    For Each cb In Application.CommandBars
        If cb.Type = msoBarTypePopup Then
            Debug.Print cb.Name
            On Error Resume Next
            For Each cbtn In cb.Controls
                Debug.Print , cbtn.Caption, cbtn.id
            Next
            On Error GoTo 0
            ' Uncomment the following line to stop at a
            ' specific context menu:
            'If cb.Name = "Cell" Then Stop
        End If
    Next
End Sub