|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
网上看到的大神验证身份证号
Private Sub Worksheet_Change(ByVal Target As Range)
'限定作用域
On Error Resume Next
If Application.Intersect(T, Range("a2:a65536")) Is Nothing Or T.Value = "" Then Exit Sub
'限定身份证号是18位
If Len(T) <> 18 Then
Application.EnableEvents = False
MsgBox "身份证号不是18位,请重新输入"
T.Select
T.ClearContents
Application.EnableEvents = True
Exit Sub
End If
'校验前17位是否为数字/检验第18位校验码是否正确
arr = Array("7", "9", "10", "5", "8", "4", "2", "1", "6", "3", "7", "9", "10", "5", "8", "4", "2")
brr = Array("1", "0", "x", "9", "8", "7", "6", "5", "4", "3", "2")
For h = 1 To 17
j = Mid(T, h, 1)
If IsNumeric(j) = False Then
Application.EnableEvents = False
MsgBox "您可能输入了非法字符"
T.Select
T.ClearContents
Application.EnableEvents = True
Exit Sub
End If
s = s + j * arr(h - 1)
Next
k = s Mod 11
If Mid(T, 18) <> brr(k) Then
Application.EnableEvents = False
MsgBox "校验码不符,请重新输入"
T.Select
T.ClearContents
Application.EnableEvents = True
Exit Sub
End If
'限定出生日期范围为1929.1.1-2000.1.1/消息框提示再次确认出生日期、性别
d = Format(Mid(T, 7, 8), "0000-00-00")
xb = Mid(T, 17, 1)
If d > #1/1/1929# And d < #1/1/2000# Then
If InStr("13579", xb) > 0 Then
MsgBox "该同志出生于:" & Format(Mid(T, 7, 8), "0000年00月00日") & ",性别为男,请最后确认一遍"
Else
MsgBox "该同志出生于:" & Format(Mid(T, 7, 8), "0000年00月00日") & ",性别为女,请最后确认一遍"
End If
Else
Application.EnableEvents = False
MsgBox "输入的出生时间可能有误,请重新输入"
T.Select
T.clearconcents
Application.EnableEvents = True
End If
希望和我的现在代码合并使用
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 16 And Target.Column <> 1 Then Exit Sub
If Target.Row = 2 Then Exit Sub
If Target.Value = "" Then
Target.Offset(, 1).Value = ""
Exit Sub
End If
If Target.Value = x Then Exit Sub
If Target.Count > 1 Then Exit Sub
Target.Offset(, 1) = Date
If Target.Column = 16 And Target = "入职" Then
Target.EntireRow.Copy Sheets("入职人员").Range("A65536").End(3)(2)
End If
End Sub
请大神指导!
|
|