Getting Data Validation to execute when using Paste Special... Val

[Bob]

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

 

[Neptune Dinosaur]

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.

 

[Javed]

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










- Show quoted text -

use
Target.PasteSpecial paste:=xlpastevalues
Target.PasteSpecial paste:=xlpastevalidation

 

[Bob]

Javed - can you tell me where exactly in my code I should insert:

Target.PasteSpecial paste:=xlpastevalues
Target.PasteSpecial paste:=xlpastevalidation

What lines in my code, if any, should I delete/replace?

Thanks,
Bob Z.