I have created data validations sheet and want to protect from getting
spoiled through cut, paste, clear, etc. I have written following code but
disabling the "Clear - All" and "Clear - Formats" is not working. Can
somebody please help.

Private Sub Worksheet_Activate()
Dim eCtrl As CommandBarControl
On Error Resume Next
For Each eCtrl In Application.CommandBars
eCtrl.FindControls(ID:=21).Enabled = 0 'Disable Ctrl-x
eCtrl.FindControls(ID:=1964).Enabled = 0 'Disable Clear - All
eCtrl.FindControls(ID:=872).Enabled = 0 'Disable Clear - Formats
Next eCtrl
With Application
If .CutCopyMode = xlCut Then .CutCopyMode = False
.OnKey "^x", ""
.CellDragAndDrop = False
.CopyObjectsWithCells = False
End With
End Sub

--
Thanx & regards,
Asif

Why not use the selection change event. It the TARGET has data validation

application.CutCopyMode = False

--
regards,
Tom Ogilvy


"Asif" wrote:

I have created data validations sheet and want to protect from getting
spoiled through cut, paste, clear, etc. I have written following code but
disabling the "Clear - All" and "Clear - Formats" is not working. Can
somebody please help.

Private Sub Worksheet_Activate()
Dim eCtrl As CommandBarControl
On Error Resume Next
For Each eCtrl In Application.CommandBars
eCtrl.FindControls(ID:=21).Enabled = 0 'Disable Ctrl-x
eCtrl.FindControls(ID:=1964).Enabled = 0 'Disable Clear - All
eCtrl.FindControls(ID:=872).Enabled = 0 'Disable Clear - Formats
Next eCtrl
With Application
If .CutCopyMode = xlCut Then .CutCopyMode = False
.OnKey "^x", ""
.CellDragAndDrop = False
.CopyObjectsWithCells = False
End With
End Sub

--
Thanx & regards,
Asif

Hi Tom,

Thank you for your response. I want to disable these editings for a
particular worksheet only and re-enable it when the worksheet is de-activate.
In addition, if I have to use changeselection event, then I will have to name
many ranges as well.

--
Thanx & regards,
Asif


"Tom Ogilvy" wrote:

Why not use the selection change event. It the TARGET has data validation

application.CutCopyMode = False

--
regards,
Tom Ogilvy


"Asif" wrote:

I have created data validations sheet and want to protect from getting
spoiled through cut, paste, clear, etc. I have written following code but
disabling the "Clear - All" and "Clear - Formats" is not working. Can
somebody please help.

Private Sub Worksheet_Activate()
Dim eCtrl As CommandBarControl
On Error Resume Next
For Each eCtrl In Application.CommandBars
eCtrl.FindControls(ID:=21).Enabled = 0 'Disable Ctrl-x
eCtrl.FindControls(ID:=1964).Enabled = 0 'Disable Clear - All
eCtrl.FindControls(ID:=872).Enabled = 0 'Disable Clear - Formats
Next eCtrl
With Application
If .CutCopyMode = xlCut Then .CutCopyMode = False
.OnKey "^x", ""
.CellDragAndDrop = False
.CopyObjectsWithCells = False
End With
End Sub

--
Thanx & regards,
Asif