VBA code required to delete Right click menu over SHAPES

[mchilapur]

Dear all,

I am using below code to delete the RIGHT CLICK menu items when clicked over any cell.
How best i can modify this code to delete the RIGHT CLICK menu items when clicked over any SHAPES (Say an Image or rectangle) as well...?


Sub RMenus_Remove()


Dim myCommandBar As Office.CommandBar
Dim ctrl As Office.CommandBarControl
Dim i As Integer, j As Integer
Dim a(20) As String

'//A list of right click menu items I don't need to see
a(0) = "Pic&k From Drop-down List..."
a(1) = "Add &Watch"
a(2) = "&Create List..."
a(3) = "&Hyperlink..."
a(4) = "&Look Up..."
a(5) = "Cu&t"
a(6) = "Clear Co&ntents"
a(7) = "&Format Cells..."
a(8) = "&Paste"
a(9) = "Paste &Special..."
a(10) = "Name a &Range..."
a(11) = "&Insert..."
a(12) = "&Delete..."


'//Tag the above list of inbuilt controls with my tag -
' my remove procedure will then remove them just like my custom controls
For Each myCommandBar In Application.CommandBars
If myCommandBar.Name = "Cell" Then
For Each ctrl In myCommandBar.Controls
For j = 0 To UBound(a)
If ctrl.Caption = a(j) Then


ctrl.Delete
Exit For
End If
Next j
Next ctrl
End If
Next myCommandBar




End Sub

Any urgent response will be of great help.

 

[mchilapur]

Cross posted



VBA code required to delete Right click menu over SHAPES
http://www.mrexcel.com/forum/excel-...ired-delete-right-click-menu-over-shapes.html
VBA code required to delete Right click menu over SHAPES

 

[Jaafar Tribak]

If you protect the worksheet then right-clicking the shape doesn't bring up the context menu

 

[phillfri]

Unfortunately this is not true for the worksheet Textbox - which is a shape object. Disabling the shapes menu does not disable shape textbox right-click menus. And even if a worksheet is protected, Excel will still show a right-click menu on a worksheet textbox, which allows the user to edit the textbox. I think this is a bug (or design failure) in Excel worksheet protection. If I put a worksheet textbox on a protected sheet to provide instructions to the user, why in the world would I want any user to be able to edit the Textbox on a protected worksheet?

If you protect the worksheet then right-clicking the shape doesn't bring up the context menu