4

I am using data validation on a column by a predefined list.

but it can be easily fooled or bypassed by pasting (Ctrl+V) data from other source.

So how can i protect to user can't enter wrong data or outside from my validation list.

Thanks

Improve this question
2
  • How are you protecting it now?
    – gronostaj
    Jul 9, 2013 at 19:35
  • Not really an answer, but you can use ActiveSheet.CircleInvalid to highlight the invalid values.
    – iDevlop
    Jun 12, 2019 at 7:17

3 Answers 3

Reset to default
2

Rahul, I am working on a similar problem right now. Having read a number of the Excel forums and the MS help boards on this issue, I can tell you that it's really TWO problems:

A) a standard copy-paste in Excel copies the validations from the source to the destination. If you don't have any safeguards, this wipes out the validation from your special column when a user pastes into it. B) Excel only performs validation on single cells and not on paste actions. Pretty weak.

I can solve problem A for you; I think you will need VBA to do custom validation for problem B. See the links at the bottom of this post for some ideas; I'm not a pro at VBA so I can't really say which will work for you.

For problem A: go to the Review tab and turn Protection on at either the sheet or workbook level :) I haven't seen anyone else mention this solution, but it works really well. When protection is turned on, the user cannot overwrite the validations in your column with a paste.

The default rights for a user in a protected sheet are pretty minimal. If your users need a lot of rights, like the ability to delete rows, just enable the rights they need in the Protect dialog by checking the checkboxes. I have everything enabled except Insert Hyperlinks and I still can't overwrite validation when protection is turned on.

Here are some relevant links for you:

http://www.mrexcel.com/forum/excel-questions/64596-data-validation-why-does-not-work-when-pasting-values.html

http://spreadsheetpage.com/index.php/tip/ensuring_that_data_validation_is_not_deleted/

https://social.msdn.microsoft.com/Forums/office/en-US/961e3921-5fd2-430e-bf50-aecb75a4fe05/excel-cell-data-validations-fails-on-copypaste

http://www.mrexcel.com/forum/excel-questions/519807-pasting-into-cell-data-validation.html

Improve this answer
1
  • 1
    Thanks for the tip about protecting the sheet to preserve data-validation rules. Although that approach works, pasting invalid values from elsewhere won't result in an error-alert even if one is set up for a given rule from the Error Alert tab in the Data Validation dialog box, so... at least in Office365, if you paste a value, you won't see a popup message and won't be forced to revert it unlike if you had simply typed something invalid.
    – koyae
    Sep 4, 2021 at 6:22
1

Try using paste hooks/handlers to prevent this from occurring.

It's something of a hack, but in your VBA you can bind custom functions to the commonly used key/click actions that fire "paste" events. Those custom functions can access the selection affected by the paste, and fire validation rules/custom subs on it immediately. For examples of this functionality, try this guide (especially the section farther down titled "Validation").

This doesn't prevent users from pasting invalid data in the first place, but results in the immediate revocation of invalid data, which might be close enough to the same thing for your purposes.

Improve this answer
1

If intercepting Paste events seems complex then an alternative solution is to just use the BeforeSave event of the workbook. In this, check the value(s) and, if any are incorrect, cancel the Save by setting 

Cancel = True

You could use the Deactivate event of a worksheet but this doesn't offer a simple way to cancel the deactivation, but a warning MsgBox could be shown.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .