Protecting validation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Asif - 27 Sep 2007 16:13 GMT

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

Signature

Thanx & regards,
Asif

Reply to this Message

Tom Ogilvy - 27 Sep 2007 16:54 GMT

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

application.CutCopyMode = False

Signature

regards,
Tom Ogilvy

> I have created data validations sheet and want to protect from getting
> spoiled through cut, paste, clear, etc. I have written following code but
[quoted text clipped - 16 lines]
> End With
> End Sub

Reply to this Message

Asif - 27 Sep 2007 17:09 GMT

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.

Signature

Thanx & regards,
Asif

> Why not use the selection change event. It the TARGET has data validation
>
[quoted text clipped - 20 lines]
> > End With
> > End Sub

Reply to this Message