I have a file which has Data Validation as list so that the users can only
select from the list for the sake of data integrity.

Some people are copying and pasting the data which replaces the data
validation. I protected the sheet but that doesn't allow me to select any of
the list items. So I unlocked the cells that have data validation. Then
Protected my sheet. That doesn't replace the data validation but it still
allows the user to over write the exisiting entry.

What I really want is that Once the data validation has been set and the
sheet has been protected then I don't want anyone to copy and paste the data
on the cells that contain the data validation.

Some help will be appreciated!!

Kindest Regards

Rajeev Rawat

On May 14, 9:37 pm, Raja wrote:
I have a file which has Data Validation as list so that the users can only
select from the list for the sake of data integrity.

Some people are copying and pasting the data which replaces the data
validation. I protected the sheet but that doesn't allow me to select any of
the list items. So I unlocked the cells that have data validation. Then
Protected my sheet. That doesn't replace the data validation but it still
allows the user to over write the exisiting entry.

What I really want is that Once the data validation has been set and the
sheet has been protected then I don't want anyone to copy and paste the data
on the cells that contain the data validation.

Some help will be appreciated!!

Kindest Regards

Rajeev Rawat

One way is to use the worksheet's Selection_Change Event to set the
CutCopyMode of Excel equal to True or False (doesn't matter which)
whenever the validated cells are selected, eg where the validated cell
is A1...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.CutCopyMode = True
End If
End Sub

This method of course fails should the user choose not to allow macros
when the workbook is opened.

To try this out...

Copy the code, Right click the worksheet tab, choose "View code", then
paste it into the code module, then Alt+F11 to return to Excel.

Ken Johnson