VBA - Remove almost all right click context menu options

[kgkev]

I have removed most of them, I just want to keep "Copy" and then add my own macros to the right click menu.

However I have not been able to remove Paste options or Link

Any ideas what the correct name for these components is?
Or maybe a neater way to "removed all" <> Copy

Private Sub Workbook_Open()
    Application.CommandBars("Cell").Reset
    Application.CommandBars("Cell").Controls("Cut").Delete
    Application.CommandBars("Cell").Controls("Smart Lookup").Delete
    Application.CommandBars("Cell").Controls("Translate").Delete
    Application.CommandBars("Cell").Controls("Paste Special...").Delete
    Application.CommandBars("Cell").Controls("Insert...").Delete
    Application.CommandBars("Cell").Controls("Delete...").Delete
    Application.CommandBars("Cell").Controls("Clear Contents").Delete
    Application.CommandBars("Cell").Controls("Quick Analysis").Delete
    Application.CommandBars("Cell").Controls("Filter").Delete
    Application.CommandBars("Cell").Controls("Sort").Delete
    Application.CommandBars("Cell").Controls("Insert Comment").Delete
    Application.CommandBars("Cell").Controls("Format Cells...").Delete
    Application.CommandBars("Cell").Controls("Pick From Drop-Down List...").Delete
    Application.CommandBars("Cell").Controls("Define Name...").Delete
    'Application.CommandBars("Cell").Controls("Link").Delete

 

[bobsan42]

try one of these:
PasteMenu
PasteGallery
PasteGalleryMini

 

[kgkev]

Application.CommandBars("Cell").Controls("PasteMenu").Delete
Application.CommandBars("Cell").Controls("PasteGallery").Delete
Application.CommandBars("Cell").Controls("PasteGallerymini").Delete

thanks for the suggestions, unfortunately none of them worked.
I created a routine that gets the caption for each context menu item

Sub getname()
For Each Item In Application.CommandBars("Cell").Controls
    MsgBox Item.Caption
Next Item
End Sub

I managed to find the paste one...

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

but still can't identify "link"

 

[kgkev]

found it....

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

 

[bobsan42]

Sorry, I was thinking idMSO and Ribbon-wise
However I think another way to do it instead of trying to figure each name:

Sub ClearContMenuButCopy()
Dim item1  As CommandBarControl
    On Error Resume Next
    For Each item1 In Application.CommandBars("Cell").Controls
        With item1
            If Replace(.Caption, "&", "") <> "Copy" Then .Delete
        End With
    Next item1
End Sub

 

[rediffusion]

bobsan42

[FONT=&quot] Hello there! Can you help me too?
How can I move the button "Keep original column width" from 'Paste special' to "The paste options" ?

[/FONT]

 

[bobsan42]

At the moment this is the closest I can get to what you want to achieve:

Application.CommandBars("Cell").Controls.Add msoControlButton,19086,,4

Below is a code that can list the controls and commandbars (start it against a blank sheet).

Sub getNamesCommandbars()
    On Error Resume Next
    Dim cb As CommandBar, cbcon As CommandBarControl, cbm As CommandBarPopup
    Dim rng As Range: Set rng = ActiveSheet.Range("D1") 'Activecell
    rng.Resize(1, 11) = Split("index,command bar name,type,context,controls,ID,Caption,Tag,TypeName,Tooltip,Type", ",")
    Set rng = rng.Offset(1)


    For Each cb In Application.CommandBars
        With rng
            .Value = cb.Index
            .Offset(, 1).Value = cb.Name
            .Offset(, 2).Value = cb.Type
            .Offset(, 3).Value = cb.Parent
            .Offset(, 4).Value = cb.Controls.Count
        End With
        Set rng = rng.Offset(1)
        For Each cbcon In cb.Controls
            With rng
                .Offset(, 4).Value = cbcon.Index
                .Offset(, 5).Value = cbcon.ID
                .Offset(, 6).Value = cbcon.Caption
                .Offset(, 7).Value = cbcon.Tag
                .Offset(, 8).Value = TypeName(cbcon)
                .Offset(, 9).Value = cbcon.TooltipText
                .Offset(, 10).Value = cbcon.Type
                .Offset(, 11).Value = cbcon.DescriptionText
            End With
            Set rng = rng.Offset(1)
        Next cbcon
    Next cb
    Set rng = Nothing
    Set cb = Nothing
    Set cbcon = Nothing
End Sub

 

[rediffusion]

I think this is better method. But none of them suits me.

 

[kgkev]

Might be a "Dirty" work around....but how about recreating the action of the "Keep column Width" in a new macro.

Then adding that control to the right click menu.

This is what mine looks like now:

Private Sub Workbook_Open()


Application.CommandBars("Cell").Reset   'resets menu
For Each Item In Application.CommandBars("Cell").Controls    'removes all right click menu items
    Item.Delete
Next Item
    
    With Application.CommandBars("Cell").Controls.Add(Temporary:=True)
        .Caption = "Main Update"
        .Style = msoButtonCaption
        .OnAction = "Main_Update"
    End With
[U][B]*Repeat adding menu items.....[/B][/U] 
End Sub

 

[rediffusion]

kgkev

Not working