Disable Delete/Edit/Clear Comment using VBA

Hello,
I have use below code to protect all worksheets and in the same time disable delete AND edit comment. I worked out that some of the protection options (AllowXXX) mess up disable function. I want the worksheets to be protected, but still allow users to unhide columns/rows and sort.
Also I noticed that despite the code below users can still go to "Editing" top Excel menu and use Clear Comment, which makes below effort useless.
Will appreciate any comment. Thank you PK


[Sub ProtectAll()
Dim ws As Worksheet
Dim myHaslo As String
Dim objCTRL As CommandBarControl

myHaslo = "123"

For Each ws In Worksheets


With Application.CommandBars("Cell")
For Each objCTRL In .Controls
If InStr(1, "Delete Co&mment/&Edit Co&mment", objCTRL.Caption) > 0 Then
objCTRL.Enabled = False

End If
Next objCTRL
End With
Set objCTRL = Nothing

ws.Protect Password:=myHaslo _
, DrawingObjects:=False, Contents:=True, Scenarios:=False _
, UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=True, AllowFormattingRows:=True _
, AllowInsertingColumns:=False, AllowInsertingRows:=False, AllowInsertingHyperlinks:=False _
, AllowDeletingColumns:=False, AllowDeletingRows:=False, AllowSorting:=True, AllowFiltering:=True _
, AllowUsingPivotTables:=True

Next ws

End Sub][/CODE]

In the meantime I've found some coding to deactivate worksheet menu - my problem is that it doesn't work - thank you for your help.

Please Login or Register  to view this content.