VBA - Remove almost all right click context menu options

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,291
Office Version
  1. 365
Platform
  1. Windows
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


Code:
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
 
Still working for me every day...
Please note the .onaction = "MAIN UPDATE" is a reference to one of my own VBA routines that is added to my right click menu

Code:
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
[COLOR=#ff0000]        .OnAction = "Main_Update"[/COLOR]
    End With
[U][B]'*Repeat adding menu items.....[/B][/U] 
End Sub
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top