Как защитить данные от сбоя при копировании и вставке данных?

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.

Если перехват событий вставки кажется сложным, то альтернативное решение состоит в том, чтобы просто использовать BeforeSaveсобытие рабочей книги. В этом случае проверьте значения и, если они указаны неверно, отмените параметр «Сохранить путем».

Cancel = True 

Вы можете использовать Deactivateсобытие рабочего листа, но это не предлагает простой способ отменить деактивацию, но MsgBoxможет быть показано предупреждение .

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