Validation, EXCEL, and Copy/Paste

[vladk]

Hi,

I set Validation to a cell. Say, the value must be always equal to the date of 09/09/2005. When I try to enter different value to this cell, the Validation works. When I try to paste that wrong value into that cell, Validation does not work. Anybody knows how to awake Validation not for manual input only, but also for Copy/Paste?

Thank you

vladk

 

[SkipVought]

Hi,

Data/Validation is a FORMAT. Just as you can "change" the format of a cell by COPYING another format from one cell to another, that's what happens when you COPY a date from a cell that does not contain the same Data/Validation.



Skip,

_[red][/red]

 

[vladk]

SkipVought,

Well, I am not talking about the date format only but also about the very value of "09/09/2005". Why validation does not let me type in just "1" and lets me paste that "1"? And how to avoid this?

Thank you

vladk

 

[SkipVought]

Notice that the Data/Validation is NO LONGER THERE in the cell that you copied to because you COPIED another format over the Data/Validation.

Skip,

_[red][/red]

 

[vladk]

SkipVought,

No, unfortunately, I cannot agree with you. The Data/Validation is still there. I copy just value and not necessarily from the excel cell.

Thank you

vladk

 

[SkipVought]

You originally stated, "Copy/Paste"

For Paste, the Data/Validation format is replaced.

For Paste Special - Values, it allows the value, for some reason, but it cannot be edited without correcting.

The only way that Data/Validation works is when the cell is EDITED.

Skip,

_[red][/red]

 

[vladk]

SkipVought,

Right, this was my impression. I was wandering if it was just my impression or it was by design. I am still interested why Validation is disabled for Paste Special and BTW for "Copy/Paste", as I originally stated. The "Copy/Paste" preserves the original Validation when you Copy from some other editor, not from EXCEL, but not engages it. So, it's unclear, why Microsoft disables Validation for Copy/Paste (Paste Special) while preserving it? What was the purpose in such a design?

Thank you again!

vladk

 

[combo]

Unfortunatelly, validation does not work when copying data (also values, that does not delete validation). (It does not work with formulas too.)

You could use Worksheet_Change event and replicate validation by code, if necessary, use Application.Undo.

Have not tested for all cases, but this works (worksheet's module, validation in A1):

Private bCutCopyMode As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing And bCutCopyMode = True Then
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = xlCut Or Application.CutCopyMode = xlCopy Then
    bCutCopyMode = True
Else
    bCutCopyMode = False
End If
End Sub

combo

 

[SkipVought]

From Excel HELP...
[tt][highlight blue][white]
Data validation is designed to display messages and prevent invalid entries only when users type data directly in a cell. When data is copied, filled, or calculated by a formula, the messages don't appear.[/white][/highlight]
[/tt]

Skip,

_[red][/red]

 

[vladk]

combo,

Thank you for the reply. Yes, this is what we are discussing about: the useless of Validation when copying data. Thank you for your code. In this particular case, I am interested in manual copying but I will save it for the future.

Thank you!

vladk

 

[vladk]

SkipVought,

Thank you for that critical sentence. For some reason, I cannot locate it in my help file. That sentence answers very important question. But why they did not extend it for copying? Well, this is may be outside of the scope of my topic.

Thank you. It was very interesting to discuss it!

vladk

 

[SkipVought]

It was listed under Troubleshoot data validation

Skip,

_[red][/red]