Prev: Create CSV
Next: Use UDF In Conditional Formatting
From: Bob on 30 Apr 2010 07:58
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 <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: Create CSV
Next: Use UDF In Conditional Formatting