Data validation is designed only to catch input that a user types directly
into a cell. Pasted data and data that is dropped in by a VBA procedure will
always bypass the validation setup.
--
Time is just the thing that keeps everything from happening all at once


"Bob" wrote:

I know that when data is normally pasted into a cell that contains Data
Validation, the Data Validation rule is deleted/cleared.

However, if I use Paste Special... Values, for example, the Data Validation
rule is preserved, although it does not execute. It's only if I subsequently
edit the cell (after having performed Paste Special... Values) that the Data
Validation rule executes.

The code below causes copied data to be pasted only as a value (thereby
preserving the Data Validation rule). What I can't seem to figure out is how
to cause the Data Validation rule to be executed after the data has been
pasted.

Any help would be greatly appreciated.

Thanks,
Bob Z.
-----------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim myValue As String
On Error Resume Next
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = Trim(myValue)
.CutCopyMode = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub