如何在复制和粘贴数据时防止数据验证失败？

Question

我正在通过预定义列表对列进行数据验证。

但可以通过粘贴（Ctrl+V）来自其他来源的数据轻松地欺骗或绕过它。

那么我该如何保护用户不会输入错误的数据或超出我的验证列表的范围呢？

谢谢

Answer 1

Rahul，我现在正在处理类似的问题。在阅读了有关此问题的大量 Excel 论坛和 MS 帮助板后，我可以告诉你，这实际上是两个问题：

A) Excel 中的标准复制粘贴将验证从源复制到目标。如果您没有任何安全措施，当用户粘贴到特殊列中时，验证就会被清除。B) Excel 仅对单个单元格执行验证，而不对粘贴操作执行验证。相当弱。

我可以为您解决问题 A；我认为您需要 VBA 对问题 B 进行自定义验证。请参阅此帖子底部的链接获取一些想法；我不是 VBA 专家，所以我真的不能说哪一个对你有用。

对于问题 A：转到“审阅”选项卡并在工作表或工作簿级别启用“保护” :) 我还没有看到其他人提到这个解决方案，但它确实很有效。当保护打开时，用户无法使用粘贴覆盖列中的验证。

受保护工作表中用户的默认权限非常少。如果您的用户需要很多权限，例如删除行的能力，只需在“保护”对话框中选中复选框即可启用他们所需的权限。我启用了除“插入超链接”之外的所有功能，但当保护打开时，我仍然无法覆盖验证。

以下是一些相关链接：

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

Answer 2

尝试使用粘贴钩/处理程序来防止这种情况发生。

这有点像 hack，但在 VBA 中，您可以将自定义函数绑定到触发“粘贴”事件的常用按键/点击操作。这些自定义函数可以访问受粘贴影响的选择，并立即触发验证规则/自定义子集。有关此功能的示例，请尝试本指南（特别是下面标题为“验证”的部分）。

这并不能首先阻止用户粘贴无效数据，但会导致立即撤销无效数据，这对于您的目的来说可能与同一件事非常接近。

Answer 3

如果拦截粘贴事件看起来很复杂，那么另一种解决方案就是只使用BeforeSave工作簿的事件。在此，检查值，如果有任何不正确的值，则通过设置取消保存

Cancel = True

您可以使用工作表事件，但这并不能提供取消停用的简单方法，但可以显示Deactivate警告。MsgBox