Copy Validation From One Range To Another

[New_to_VBA]

Does anyone know how to fix the following? The variables are predefined, the syntax for copying the validation is incorrect.

Range(P1start, P1start.End(xlToRight)).Validation.Type = P1input.Validation.Type
Range(P1start, P1start.End(xlToRight)).Validation.Formula1 = P1input.Validation.Formula1

[thomach]

Re: Copy Cell Validation From One Range To Another

Assuming P1start is a Named Range (one cell) and the cells to its right have entries so that the .End will find an end point cell: you could use this syntax.

Range("P1start").Copy
Range(Range("P1start"), Range("P1start").End(xlToRight)).PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

[thomach]

Re: Copy Cell Validation From One Range To Another

P.S. Just to be clear, if your P1Start variable is a a range (your description makes it sound like it is), then you can use:

Dim P1Start As Range

Set P1Start = Range("StartCell")
P1Start.Copy
Range(P1Start, P1Start.End(xlToRight)).PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

[New_to_VBA]

Re: Copy Cell Validation From One Range To Another

Thanks very much, I will use this for the initial fix. If anyone knows of a pure VBA way of doing this without using copy and paste then do let me know.

[Dave Hawley]

Re: Copy Cell Validation From One Range To Another

Toms code is a "pure VBA way".

[New_to_VBA]

Re: Copy Validation From One Range To Another

ok, by copying assignments of the .validation property.

[Dave Hawley]

Re: Copy Validation From One Range To Another

That is what PasteSpecial does.

[New_to_VBA]

Re: Copy Validation From One Range To Another

It is well known that the copy paste method is slow, which is why I am asking for an alternative.

[Dave Hawley]

Re: Copy Validation From One Range To Another

There is no faster method to transfer a cell Validation attributes.

[baldmosher]

Re: Copy Validation From One Range To Another

There is no faster method to transfer a cell Validation attributes.

That may well be the case, but avoiding the use of clipboard is advisable for minimising user annoyance factor. Particularly when running macros in the background. Even more so when running in a remote desktop or Citrix window where the clipboard contents have to be transferred back and forth across the network......

Try this:

Function xlU_TransferValidationList(ByRef vSource As Range, ByRef vTarget As Range) As Boolean
'v1.00 2013-08-02 13:02
'coded by baldmosher, feel free to steal and adapt
'returns True if transferred OK
'max 1 cell in vTarget and vSource
'could easily be adapted to copy validations across a whole row from a source row
'e.g.:
'    With Target.EntireRow
'        For i = 1 To .Cells.Count  'NB: this is very inefficient! Limit this to e.g. columns with headers
'            xlU_TransferValidationList .Cells(i), Domains.Rows(2).Cells(i)
'        Next i
'    End With

On Error GoTo ErrorHandler
Dim c As Byte
c = vSource.Cells.Count
If c > 1 Then GoTo ErrorHandler
With vTarget.Validation
On Error Resume Next
    .Delete
On Error Goto ErrorHandler
    .Add Type:=vSource.Validation.Type, AlertStyle:=vSource.Validation.AlertStyle, Operator:= _
        vSource.Validation.Operator, Formula1:=vSource.Validation.Formula1
    .IgnoreBlank = vSource.Validation.IgnoreBlank
    .InCellDropdown = vSource.Validation.InCellDropdown
    .InputTitle = vSource.Validation.InputTitle
    .InputMessage = vSource.Validation.InputMessage
    .ErrorTitle = vSource.Validation.ErrorTitle
    .ErrorMessage = vSource.Validation.ErrorMessage
    .ShowInput = vSource.Validation.ShowInput
    .ShowError = vSource.Validation.ShowError
End With

xlU_TransferValidationList = True

ErrorHandler:
End Function