Prevent Data Validation rule from being copied over

[dgillz]

I have a data entry spreadsheet with several data validation rules that work perfectly if the user keys in data. However if the user copies in data - which is a huge timesaver in certain cases - the copy wipes out the data validation rules for the copied range.

Is there a way to disable this part of the copy function so data validation rules do not get erased? I can use VBA if that helps.

Any insights appreciated, Excel 2010 by the way.

Software Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[SkipVought]

hi,

The solution to this issue is a VBA one. You can turn off the CutCopyMode in the Worksheet_SelectionChange event.

Do this process:
[tt]
1. Copy a cell
2. Turn on your Macro Recorder
3. Select one of your DV cells
4. Click on your Format Painter Icon (notice what happens to the dancing ants around the copied cell)
5. Turn off your Macro Recorder
[/tt]
alt+F11 toggles between the sheet and the VB Editor. Copy your code and post you question and code in forum707 for best results, along with a full description of the location(s) of your DV cell(s) and/or the Named Range(s) of your DV Cell(s).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dgillz]

I don't want to turn off copying altogether, I just don't want such copies to erase my data validation rules. Do you know of a way to do this?

Software Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[SkipVought]

Did you not examine the process I outlined? You do not want to be able to paste over a DV, right?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dgillz]

I DO want to paste DATA ONLY in there, without overridding the data validation rules.

Youe instructions rendered this code:

Range("AC13").Select
Application.CutCopyMode = False
Selection.Copy

However it let me copy over that cell, including data that was not in compliance with my data validation rules.

Software Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[SkipVought]

Sorry, I misread your requirement. My approch does disable the PASTE totally.

So you have at least two options:
[tt]
1. Manual Approch: Use Paste Special -- VALUES.
2. Code Approch: ASSUME that when you select a DV that the contents of the clip board should be pasted as VALUES and perform that paste special values.
[/tt]
Your choice.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dgillz]

It has to be the vba code approach - I don't trust the users, hence the data validation rules to begin with. But how do I either globally or with a range, restrict pasting to paste special-values?

Software Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[SkipVought]

The variable is how you have identified your DVs. I used a Named Range...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [b][MyDVs][/b]) Is Nothing Then
        If Application.CutCopyMode Then _
            Target.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}