|
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Address = "$B$5" Then
- Application.EnableEvents = False '关闭单元格触发,为下面单元格变化不再触发此程序
- ActiveSheet.DrawingObjects.Select
- Selection.Delete
- Dim Ml#, Mt#, Mw#, Mh#
- Dim Arr, i%
- Ml = Range("i4:I7").Left
- Mt = Range("i4:I7").Top
- Mw = Range("i4:I7").Width
- Mh = Range("i4:I7").Height
- Sheet39.Shapes.AddShape(msoShapeRectangle, Ml, Mt, Mw, Mh).Select
- ' Selection.ShapeRange.Fill.UserPicture _
- ' "d:\学员照片" & Target.Text & ".jpg"
- On Error Resume Next '当身份证没有时继续下一步
- Selection.ShapeRange.Fill.UserPicture _
- ThisWorkbook.Path & "\学员照片" & Target.Text & ".jpg"
- Range("B4,D4,G4:H4,B6:H6,B7:C7,B15:C15,D16:F16,G15:I15").ClearContents
- Arr = Sheet2.Range("A3:Q" & Sheet2.Range("A65536").End(3).Row)
- For i = 1 To UBound(Arr)
- If Range("B5") = Arr(i, 5) Then
- Range("B4") = Arr(i, 2)
- Range("D4") = Arr(i, 3)
- Range("G4") = Arr(i, 6)
- Range("B6") = Arr(i, 7)
- Range("B7") = Arr(i, 8)
- Range("D16") = Arr(i, 15)
- Range("B15") = Arr(i, 9)
- Range("G15") = Arr(i, 16)
- GoTo ed
- End If
- Next
- MsgBox "没有身份证号为〖 " & Range("$b$5") & " 〗的登记"
-
- End If
- ed:
- Application.EnableEvents = True
- End Sub
复制代码 |
|