Protecting validation

A

Asif

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
 
T

Tom Ogilvy

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

application.CutCopyMode = False
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top