cell validation even for values pasted into cells

[Guest]

The cell validation feature works fine when a user types a value into a cell,
but does not prevent pasting in a value that does not meet the validation
criteria. Is there a way to validate values that are pasted into a cell?

 

[Tom Ogilvy]

I think you can prevent pasting by locking the cell and protecting the
sheet, but I don't believe you can validate a pasted value unless you use
VBA code to react to the action.

 

[Guest]

Can I get some help with the VB code on this board or do I need to go to the
Excel programming board?

I know code that will test for a given value to a cell, but I don't know
how to test for pasted values even in VB.

I'm guessing that the code would go in either the worksheet sub-procedure
Worksheet_SelectionChange(ByVal Target As Range) or Private Sub
Worksheet_Change(ByVal Target As Range) and I would also guess that
Cells(col,row).value property might be used. But I have no idea how to
determine if a value is pasted or typed into a cell, or how to test pasted
values against a list of values as opposed a single value, a procedure which
is easy to do with typed values using the data validate routine.

End Sub