CODEWITHSUNDEEP
excelvbacontrols
Afshin Davoudy
Afshin Davoudy

Reputation: 181

VBA: How can I disable some options from right-click context menu

I need to

1) Disable all the following options from "Cells" right-click context menu

a.  Paste options
b.  Insert
c.  Delete
d.  Format Cell

2) Disable all the following options from "Columns" right-click context menu

a.  Paste options
b.  Insert
c.  Delete
d.  Clear Content
e.  Format Cell

3) Disable all the following options from "Rows" right-click context menu

a.  Paste options
b.  Insert Copied Cells only (Not Insert)
c.  Format Cell

4) Disable all the following options from "Rows" right-click context menu for a specific range of rows (e.g. Rows 1 to 3)

a.  Insert
b.  Delete
c.  Clear Content

5) Disable all the following option from " Form control button " right-click context menu

a.  Format Control

Appreciate any help

Upvotes: 1

Views: 2962

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

Here is a simple way to achieve what you want. For most of them you can use exactly what you see in the right click menu. For example for Insert, you see Insert.... 

Application.CommandBars("Cell").Controls("Insert...").Visible = True 'False

Similary for others. I have not found a way to disable Paste Options: or the icons in it. You can however disable the Paste Special under Paste Options: as I mentioned above using Paste Special...

Note: Well this is the least of your problems. Remember users can still use the Ribbon or shortcut keys to achieve those things which you disable ;)

Edit

Unfortunately doesn't work for me. Does it have anything to do with Tables? because cells are part of a table! – Afshin Davoudy 8 mins ago

Yes for that you have to use 

Application.CommandBars("List Range Popup").Controls("Delete").Visible = False

BEFORE

enter image description here

AFTER

enter image description here

Afshin Davoudy: how can I disable options in "Column" context menue? or Rows? (From Extended Chat)

Use

Application.CommandBars("Column").Controls("Delete").Visible = False
Application.CommandBars("Row").Controls("Delete").Visible = False

Upvotes: 3

Related Questions