VBA - Remove almost all right click context menu options

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,285
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Forum statistics

Threads
1,214,643
Messages
6,120,707
Members
448,981
Latest member
recon11bucks

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