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

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

On Apr 30, 10:28*am, Neptune Dinosaur wrote:
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- Hide quoted text -

- Show quoted text -

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

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.


"Javed" wrote:

On Apr 30, 10:28 am, Neptune Dinosaur wrote:
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- Hide quoted text -

- Show quoted text -

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

.