|
- Dim d, d1
- Sub init() '初始化
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- With Sheets(2)
- arr = .Range("a4:j12")
- For j = 1 To UBound(arr, 2)
- For i = 2 To UBound(arr)
- If Len(arr(i, j)) > 0 Then d(arr(1, j)) = d(arr(1, j)) & "," & arr(i, j)
- Next
- Next
-
- arr = .Range("a17:ay27")
- For j = 1 To UBound(arr, 2)
- For i = 2 To UBound(arr)
- If Len(arr(i, j)) > 0 Then d1(arr(1, j)) = d1(arr(1, j)) & "," & arr(i, j)
- Next
- Next
- End With
- End Sub
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If Target.Count > 1 Then Exit Sub
- c = Target.Column: r = Target.Row
- If c > 3 And r < 5 Then Exit Sub
- Call init '初始化
- If c = 1 Then
- xStr = Join(d.keys, ",")
- Else
- x = Target.Offset(, -1)
- If c = 2 Then
- xStr = Mid(d(x), 2)
- ElseIf c = 3 Then
- xStr = Mid(d1(x), 2)
- End If
- End If
- If Len(xStr) Then
- With Target.Validation
- .Delete
- .Add xlValidateList, , , xStr
- End With
- End If
- End Sub
复制代码 |
|