Prev: Checking if some cells are empty when file is being saved
Next: Enable Spelling Check
From: Bob on 29 Apr 2010 15:44
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

From: Neptune Dinosaur on 30 Apr 2010 01:28
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
>
From: Javed on 30 Apr 2010 04:51
On Apr 30, 10:28 am, Neptune Dinosaur <wor...(a)halfchopper.com> 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

 | 
Pages: 1
Prev: Checking if some cells are empty when file is being saved
Next: Enable Spelling Check