|
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Count > 1 Then Exit Sub
- If Target = "" Then Exit Sub
- If Target.Address <> "$D$3" Then Exit Sub
- Dim d, Arr, i&, pth$, ML, MT, MW, MH, shp, fnm$
- Set d = CreateObject("Scripting.Dictionary")
- Arr = Sheet2.[a1].CurrentRegion
- For i = 4 To UBound(Arr)
- d(Arr(i, 1)) = i
- Next
- pth = ThisWorkbook.Path & "\图片"
- For Each shp In ActiveSheet.Shapes
- If shp.Type = 1 Then
- shp.Delete
- End If
- Next
- If d.exists(UCase(Target.Value)) Then
- fnm = pth & UCase(Target.Value) & ".jpg"
- With Range("d4:k23")
- ML = .Left
- MT = .Top
- MW = .Width
- MH = .Height
- End With
- ActiveSheet.Shapes.AddShape(msoShapeRectangle, ML, MT, MW, MH).Select
- Selection.ShapeRange.Fill.UserPicture fnm
- [d3].Resize(1, 8) = Application.Index(Arr, d(UCase(Target.Value)), 0)
- [d3].Select
- Else
- MsgBox "没有此数据。"
- [d3].Resize(1, 8).ClearContents
- End If
- End Sub
复制代码 |
|