|
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim d As Object
- Dim rng As Range
- Dim p$, f$, y%, n$, A(), i%
- p = ThisWorkbook.Path
- y = [b3] '选择年份
- n = [f3] '选择名称
- A = Array("图片前", "图片后", "A6", "H6")
- '可能一:重建数据有效性
- If Target.Address = "$B$3" Then
- Dim k, t, str$
- '收集
- Set d = CreateObject("scripting.dictionary")
- For i = 0 To 1
- f = Dir(p & "" & A(i) & "" & y & "")
- Do While f <> ""
- Debug.Print f
- d(f) = d(f) + 1
- f = Dir
- Loop
- Next i
- k = d.keys: t = d.items
- '验证
- For i = 0 To UBound(t)
- If t(i) < 2 Then d.Remove k(i)
- Next i
- str = Join(d.keys, ",")
- '加载
- With Range("F3").Validation
- .Delete
- Application.EnableEvents = False
- If str <> "" Then
- .Add Type:=xlValidateList, Formula1:=str
- Range("F3") = "..."
- Else
- Range("F3") = "无图片"
- End If
- Application.EnableEvents = True
- End With
- End If
- '可能二:加载图片
- f = ""
- If Target.Address = "$F$3" Then
- ActiveSheet.Pictures.Delete
- For i = 0 To 1
- Set rng = Range(A(i + 2))
- f = p & "" & A(i) & "" & y & "" & n
- ActiveSheet.Shapes.AddPicture Filename:=f, _
- LinkToFile:=True, SaveWithDocument:=True, _
- Left:=rng.Left, Top:=rng.Top, _
- Width:=324, Height:=244
- Next i
- End If
- End Sub
复制代码
图片文件3.rar
(795.87 KB, 下载次数: 20)
|
|