B
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
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