On May 17, 7:51 pm, Guneet Ahuja <Guneet
wrote:
Hi,

I have unlocked some cells with data validation, now after protecting the
work sheet, when the user types in data the validation is working fine.
However it also allows user to copy - paste any value in these cells
irrespective of the vaidaion conditions. Request you to please let me know
how to correct this and make even copy - paste of data subject to data
validation?

You can't validate the pasted data, but you could prevent pasting of
data into the cells with data validation. To do that you 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