通过在excel模板文件中设置数据有效性校验,可以保证用户填写数据符合实际要求。但若用户通过复制/粘贴时,会连同数据有效性设置一并被覆盖掉。无法保证用户实际填写的信息符合要求。这里提供一种实现方案来解决此类问题。主要需要结合单元格保护设置+VBA脚本控制。
- 新建“启用宏的工作薄”,文件后缀名称为xlsm。
- 打开模板文件,在sheet1上右击,选择查看代码打开VBA编辑器
- 在VBA编辑其中将增加如下代码:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If CheckValidation(Target) = 0 Then
GoTo EndSub
End If
If Target.Validation.Value = False Then
MsgBox ("输入数值不符合数据有效性要求,请重新输入")
Application.Undo
End If
On Error GoTo 0
EndSub:
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If CheckValidation(Target) = 0 Then
GoTo EndSub
End If
If Target.Validation.Value = False Then
MsgBox ("输入数值不符合数据有效性要求,请重新输入")
Application.Undo
End If
EndSub:
End Sub
Private Function CheckValidation(ByVal Target As Range)
On Error Resume Next
CheckValidation = 0
If IsNull(Target.Validation.Formula1) = True Then
CheckValidation = 0
Else
CheckValidation = 1
End If
On Error GoTo 0
End Function
- 保存后,进行数据有效性的设置。注意:需要将下图中的勾选去除
- 选中sheet页,并设置单元格保护
- 完成。