excel如何避免因补充而破坏数据有效性？

Excel数据验证(在Excel 2013及以上版本中改名为数据验证)是一个非常方便的功能，它帮助我们让用户在单元格中输入指定的数据。但是，将数据复制并粘贴到具有数据有效性的单元格中会破坏数据有效性设置。

使用VBA码，可以避免因粘贴数据而破坏单元格数据的有效设置。我最初的想法是，如果有有效数据设置的单元格，我会在用户粘贴数据之前保存有效数据设置。然而，用户从未开始编写代码。今天在jkp-ads.com看到了实现这个功能的代码，就偷懒做了一点整理修改，在这里编译一下，供有需要的朋友参考。

为了避免粘贴操作的影响，我们必须首先捕获所有可以使用的粘贴操作命令。有许多粘贴命令，包括：

1.ctrl+V组合键。

2.Ctrl插入组合键。

3.Shift键插入组合键。

4.输入密钥。

5.功能区、菜单等中的命令。

下面是捕获粘贴操作并指定相应处理的代码。

在VBE中，插入一个名为clsCommandBarCatcher的类模块，并输入代码：

捕捉命令预置的预置，防止粘贴。

作为办公室的公共事件。按钮

子类_终止()。

设置oComBarCtl=无。

结束子级

私有子组合工具栏_单击(_。

按值控制作为办公室。命令按钮，_

CancelDefault是布尔值)

取消故障=真

现在准时，“我的粘贴值”。

结束子级

插入标准模块并输入代码：

选项特定模块

"禁用复制和粘贴。

暗淡的麦卡彻作为一个集合。

确保所有复制操作都重定向到它们自己的操作。

视网膜覆盖模式及有效性验证。

子CatchPaste()

停止捕获粘贴

设置mcCatchers=新集合。

“粘贴按钮”

AddCatch“虚拟”，22。

粘贴(拖动)

EnableDisableControl 6002，否

粘贴特殊按钮。

AddCatch“虚拟”，755。

粘贴链接按钮。

AddCatch“虚拟”，2787。

粘贴格式按钮。

AddCatch“虚拟”，369。

插入剪切单元格按钮。

AddCatch“虚拟”，3185。

插入复制单元格按钮。

AddCatch“虚拟”，3187。

粘贴

申请。onkey“^五世”，“我的帕斯捷价值观”

Ctrl插入

申请。onkey“^ { insert }”，“我的粘贴值”

换档插入件。

申请。在键“{插入}”、“我的粘贴值”上

输入'

申请。OnKey"，《我的帕斯捷价值观》

申请。在键“{输入}”、“我的粘贴值”上

修改细胞折射率模式。

如果是应用。那么CellDragAndDrop。

申请。CellDragAndDrop=false。

以防万一

结束子级

将粘贴操作重置为重置值。

子停止捕捉粘贴()

尽可能模糊。

关于错误继续。

设置麦卡彻=无。

EnableDisableControl 6002，真的。

申请。昂基“^五世”

application . onkey“{ insert }”。

application . onkey“{ insert }”。

申请。OnKey""

申请。在键“{输入}”上

申请。CellDragAndDrop=真

结束子级

添加到受监控的命令栏控件。

添加捕获物(鲭是一根绳子，lID很长)

将oCtl调暗为CommandBarControl。

将CCatcher调暗为clsCommandBarCatcher。

将oBar调暗为CommandBar。

设置oCtl=否。

关于错误继续。

set obar=application.command bars(scombarname)。

如果oBar一无所有，那么。

set obar=application.commandbars . add(scombarname，True)。

oBar。控件。添加标识：=lID

oBar。可见=真

以防万一

有了oBar

设置oCtl=。FindControl(ID:=lID:=lid，递归：=True)。

如果oCtl一无所有，

设置Octl=. controls . add(ID:=LiD:=LiD)。

以防万一

最后有

尝试通过单元格快捷菜单分别插入复制/剪切的单元格。

如果oCtl为无和(lID=3185或lID=3187)，那么。

设置octl=application.commandbars(“单元格”)。_

数据控件(id:=lid，递归：=True)

以防万一

设置CCatcher=new clsCommandBarCatcher。

设置CCatcher.oComBarCtl=oCtl。

麦卡彻斯。添加日历

设置计数器=无。

oBar。删除

设置oBar=无。

结束子级

通过所有命令打开/覆盖指定的控制预置。

private enableddisablecontrol(id很长，bEnable是布尔值)。

将oBar调暗为CommandBar。

将oCtl调暗为CommandBarControl。

关于错误继续。

对于CommandBars中的每个oBar。

设置oCtl=oBar。FindControl(ID:=lID:=lid，递归：=True)。

如果不是，那么。

OCtl。使能=b使能。

以防万一

然后

结束子级

从clsCommandBarCatcher控制的事件处理。

不同的OnKey宏调用特殊的粘贴值程序。

子MyPasteValues()。

如果应用。剪切复制模式为假。

如果MsgBox(“正常粘贴操作已被替换。您将粘贴该值(无法恢复)。您想继续吗？”_

& &vbNewLine& &“提示：如果要重命名，请使用命令替换的粘贴值按钮。”,_

VbQuestion vbOKCancel，“禁用标题演示”)=vbOK，然后。

关于错误恢复文本。

选择。粘贴特殊粘贴：=xlValues。

IsCellValidationOK选择

以防万一

Elseifapplication。那就回去吧。

关于错误继续。

选择案例应用程序。MoveAfterReturnDirection方向。

案例xlUp

ActiveCell。偏移量(-1).挑选

XlDown案例。

ActiveCell。偏移量(1).挑选

案例xlToRight。

ActiveCell。偏移量(，1).挑选

案例xlToLeft。

ActiveCell。偏移量(，-1).挑选

结束选择

以防万一

结束子级

检查要粘贴到的单元格是否违反数据验证规则。

如果违反了任何单元格验证，则返回False。

公共函数IsCellValidationOK(对象的橙色)是一个布尔值。

将oCell调暗为范围。

如果typename(橙色)“range”，则退出该函数。

IsCellValidationOK=真

橙色的每一个字母。

如果不是细胞。验证无效。

如果oCell。那么就有一个公式。

其他的

如果oCell。验证。那么值=假。

IsCellValidationOK=假

放弃

以防万一

以防万一

以防万一

然后

如果IsCellValidationOK=False，则

MsgBox "警告！"&vbNewLine&vbNewLine&_

"粘贴操作导致包含有效验证规则的一个或多个单元格中出现不符合的规则。"_

&vbNewLine&vbNewLine&_

"请检查刚才粘贴值的所有单元格，并更正错误！",_

Vbokonly解释，“禁止粘贴演示”。

范围选择

以防万一

结束功能

Submypastevalues 2007(控件是IRibbonControl，ByRefcancelDefault)。

我的粘贴值

结束子级

在此工作簿代码模块中，输入代码：

私有mdNextTimeCatchPaste粘贴为Double。

子工作簿_激活()

CatchPaste

结束子级

私有工作簿_关闭前(取消为布尔值)

停止捕获粘贴

MdNextTimeCatchPaste=now。

申请。ontimemdnexttimecatchpase，“”和此工作簿。名字和“’！捕捉粘贴"

申请。CellDragAndDrop=真

结束子级

子工作簿_停用()

停止捕获粘贴

关于错误继续。

申请。ontimemdnexttimecatchpase，“”和此工作簿。名称& " '！捕捉粘贴“，假

结束子级

子工作簿_打开()

CatchPaste

结束子级

打开工作簿时，进行相应的设置。当工作簿关闭或不是当前工作簿时，恢复相应的设置。

关闭工作簿，使用自定义用户界面编辑器打开它，并输入以下XML代码：

customUI xmlns=“http://schemas.microsoft.com/office/2006/01/customui”

命令

命令IDMso=" paste " on action=" my paste values 2007 "/

命令idmso=" paste specific " on action=" my paste values 2007 "/

Command idmso="粘贴公式" onaction="mypaste values 2007"/

命令idmso="粘贴格式" onaction="mypaste values 2007"/

命令idmso="粘贴值" onaction="mypaste values 2007"/

command idmso=" paste orders " on action=" my paste values 2007 "/

对action="mypaste values 2007 "执行命令idmso="粘贴转置"/

command idmso=" pale ink " on action=" my pastevalues 2007 "/

命令idmso=" paste specific " on action=" my paste values 2007 "/

command idmso=" pastashyperlink " on action=" mypastevalues 2007 "/

命令idmso=" pastepicturelink " on action=" my pastevalues 2007 "/

command idmso=" paste spice " on action=" my paste values 2007 "/

保存并关闭自定义用户界面编辑器。再次打开工作簿并尝试效果，如下图1所示。

图1

标准模块代码的图片版本如下：

clsCommandBarCatcher的类模块代码的图片版本：

此工作簿模块的代码图片版本：