|
发表于 2014-10-19 10:39
|
显示全部楼层
本楼为最佳答案
- Private Sub Workbook_Open()
- Call aa
- End Sub
- Public d, d2, arr
- Sub aa()
- arr = Range("a3").CurrentRegion
- Set d = CreateObject("Scripting.Dictionary")
- Set d2 = CreateObject("Scripting.Dictionary")
- For i = 2 To UBound(arr)
- zf = arr(i, 2) & "," & arr(i, 3)
- If Not d.exists(arr(i, 2)) Then
- d(arr(i, 2)) = arr(i, 3)
- Else
- d(arr(i, 2)) = d(arr(i, 2)) & "," & arr(i, 3)
- End If
- If Not d2.exists(zf) Then
- d2(zf) = arr(i, 4)
- Else
- d2(zf) = d2(zf) & "," & arr(i, 4)
- End If
- Next
- End Sub
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If Target.Address = "$B$1" Then
- With Target.Validation
- .Delete
- .Add Type:=xlValidateList, Formula1:=Join(d.keys, ",")
- End With
- End If
- If Target.Address = "$C$1" Then
- With Target.Validation
- .Delete
- .Add Type:=xlValidateList, Formula1:=d([b1].Value)
- End With
- End If
- If Target.Address = "$D$1" Then
- With Target.Validation
- .Delete
- .Add Type:=xlValidateList, Formula1:=d2([b1].Value & "," & [c1].Value)
- End With
- End If
- End Sub
复制代码 |
|