• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation list override when copy pasted

B

Brij Arora

New Member
Dear Ninjas,

I would be facing problem when using data validation list option. when user choose from list it works fine. but when they copy paste from another cell/sheet then data is not validated against list leads to erroneous result. sample data sheet attached H41:AL52 is the range with pasted data.

Brij A
 
Hi Arora ,

This is a known problem , and nothing much can be done about it ; you need to decide what is to be done.

If a range of cells has Data Validation in them , it can be list , formula or any other rule which prevents invalid data from being entered , you can copy + paste into this range ; the DV rules will not prevent the paste operation ; in the process of the paste operation , the DV rules will be deleted.

You need to decide whether this is acceptable , or should the paste operation be prevented with a message to the user ? If it is the latter , you can find more information here :

http://j-walk.com/ss/excel/tips/tip98.htm

It is also possible to have a Worksheet_Change event procedure , which will undo the deletion of the DV rules , and yet carry out the paste , as long as the data being pasted is conforming to the DV rules.

Narayan
 
Thanx Narayan,

tried the another workaround too , this too throwing error of "out of stack space" screen shot attached.

Brij A
 

Attachments

  • error.png
    error.png
    168.1 KB · Views: 45
Hi Arora ,

This is possible if you do not disable repeated executions of the Worksheet_Change event procedure due to a change to any worksheet cell from within the procedure.

If you are changing any worksheet cell in a Worksheet_Change event procedure , then you should have :

Application.EnableEvents = False

prior to making the change ; restore the setting before you exit from the procedure by :

Application.EnableEvents = True

Narayan
 
By putting this in code (May be at wrong place:rolleyes: ), I am not able to enter any data not even data from list . it's showing the error message meant for copy paste.
I have put this at sheet module.
here is the code

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    Application.EnableEvents = False
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
 
    End If
    Application.EnableEvents = True
 
End Sub
 
Private Function HasValidation(r) As Boolean
'  Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
 
Hi Arora ,

Have you tried changing it as follows :
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.EnableEvents = False
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
    Application.EnableEvents = True
End Sub
Narayan
 
Hi Arora ,

I am clueless with your file ; which cells have Data Validation rules in them , and which is the range you are copying from ?

You have earlier mentioned that the paste to range is H41:AL52.

Can you give the other details ?

As it stands , the named range ValidationRange is defined but does not contain any validation rule ! This is why the Worksheet_Change event procedure is not allowing you to enter any data in the range , since it thinks the DV rules have been deleted because of the data entry !

First remove the Worksheet_Change event procedure ; introduce the DV rules in the named range , and then re-insert the Worksheet_Change event procedure. Then check with :

a) Valid data entry through the keyboard
b) Invalid data entry through the keyboard
c) Copy + paste operation

Narayan
 
Hi, Brij Arora!
Unless I'm missing something you have 2 workarounds:
1) Use copy & paste special / values instead of copy & paste, this will maintain your data validation conditions even they won't be run over the pasted data until you try to edit the cells
2) Tweak the posted code for change event to do a copy & paste special / values instead of either overwriting data validation conditions or rejecting the paste.
Are these options suitable?
Regards!
 
Hi Narayan,
thanx for putting that much effort.
here is the validation range
C:\Users\Brij\Pictures\validation-range.jpeg

or h6:al105
and earlier range defined in previous file is show the breach of DV rules by pasting example data.

Sheet attached is used for Attendance DV list is applied where user can choose P,L,AWOL,CO etc. reference through other sheet.

After applying the suggested turn around
a) works fine
b) works fine
c) able to paste ( overriding dv rule)

is there any thing else I can explain to understand better ?

attaching the updated file for ready reference .


@SirBJ7
1) Use copy & paste special / values instead of copy & paste, this will maintain your data validation conditions even they won't be run over the pasted data until you try to edit the cells
Click to expand...
I will appreciate if you explain will it help us to reach the desired outcome.
 

Attachments

  • attendance.xlsm
    35.3 KB · Views: 62
Hi Arora ,

Thanks for all the clarifications ; if I understand the results , only (c) is a problem ; you need to say what should be the policy , should the overwriting of the DV rules be allowed or should the copy + paste be prevented ?

Or do you want additional code which checks whether the copy + paste will infringe the DV rules or not ; if it infringes , then it prevents the copy + paste , if not , it allows it. Of course , I do not know how far this approach will be successful , but I can try ; only writing this code will take some time.

Narayan
 
Hi Narayan,

surely second approach is more apt and there is no hurry, and it's also fruitful to prevent copy + paste.
your effort to resolve the problem is worth appreciating.


Brij A
 
Hi Arora ,

I have checked the file you uploaded ; I added the DV rule for the entire ValidationRange , and things seem to be working properly ; can you verify ?

Any copy + paste operation will succeed as long as it does not delete the DV rules ; thus , as long as you copy from any place within the named range ValidationRange to any other place within the range , there will not be any problem.

As you add new rows to the table , each new row comes with the DV rule in place.

Can you confirm ?

Narayan
 
Hi Narayan,
I have checked the file , it's working fine for validation range. but problem exist it's not allowing to enter data in whole work sheet.
 
Hi Arora ,

Can you say specifically in which area of the worksheet it does not allow data entry ?

In this area , is there any data validation ?

Narayan
 
Brij Arora wrote:
...
@SirBJ7
1) Use copy & paste special / values instead of copy & paste, this will maintain your data validation conditions even they won't be run over the pasted data until you try to edit the cells​
I will appreciate if you explain will it help us to reach the desired outcome.
Click to expand...
Hi, Brij Arora!
I tried to mean that doing a normal copy & paste you lose all the data validation conditions set in the target (copied) range. But if you copy and then paste special, with value options, you just replace the values, keeping the other cell attributes intact, data validation conditions among them.
That was a workaround as written in 1), but the ideal solution is to tweak the code as suggested in 2).
Regards!
 
Hi,

I usually use a workaround for this problem. I have the data validation in 1 column, and put that column in unprotected. Then i select the next column, and put that protected.
Then i merge the two columns. i protect the worksheet, and voilà. You can use the data validation, but you can not paste in it.

Hope this helps

(Also, may be interesting. In stead of using merge and centre for each line, it's easier to add the command "merge across" to the ribbon. This allows you to select 2 columns over a certain range to merge them. This does not centre though, so you will have to do that separately)

Sincerely,

Pofski
 
Pofski said:
Hi,

I usually use a workaround for this problem. I have the data validation in 1 column, and put that column in unprotected. Then i select the next column, and put that protected.
Then i merge the two columns. i protect the worksheet, and voilà. You can use the data validation, but you can not paste in it.

Hope this helps

(Also, may be interesting. In stead of using merge and centre for each line, it's easier to add the command "merge across" to the ribbon. This allows you to select 2 columns over a certain range to merge them. This does not centre though, so you will have to do that separately)

Sincerely,

Pofski
Click to expand...



Can you add example sheet for better understanding .
Brij A
 
Sure :)

the page is protected, and as you will be able to see, you can use the data validation.
But you can't paste in the cells

Hope this helps out
 

Attachments

  • DatavalidationTrick.xlsx
    10.2 KB · Views: 233
Hi All,

Following this conversation as I am trying to do something similar with an excel:

Requirement: Copy-Paste Needs to be prevented if DV Rules are not met. I copied the VBA Code, but on my excel its giving the following error:

range of object _worksheet failed

And then it allows the copy to happen. I am not very familiar with vba, so any help will be appreciated.

Thank you,

Tushar.

Edit: I found out why this was being thrown: As I had not defined the range in my excel: Duh! But now it is giving me an error when I past anything not just the ones flouting the DV rules.

Edit: Fixed the above as well. What is required to implement this across a few columns rather than just one. Just increase the range of the Name Range?
 
Last edited:
@Tushar Sharma
Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!

PS: Please don't answer here at this thread.
 
NARAYANK991 said:
Hi Arora ,

Please see the attached file.

Narayan
Click to expand...

Hi Narayan,

It seems that the VBA only works when data validation in the selected range is identical. If the range has different type of validations it does not run.

Is there a way to adapt the VBA so it can work with multiple types?

BR

Bob
 
You must log in or register to reply here.
Back
Top