|
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 And Target.Column <> 2 Then Exit Sub
Dim i%, arr, x$
Dim d As New Dictionary
arr = Sheets("基础信息").Range("A1:B" & Sheets("基础信息").Range("B65536").End(xlUp).Row)
For i = 1 To UBound(arr)
x = arr(i, 1): y = arr(i, 2)
If Not d.Exists(x) Then Set d(x) = New Dictionary
d(x)(y) = ""
Next
If Target.Column = 1 Then
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(d.Keys, ",")
End With
Target.Offset(0, 1) = ""
Else
x = Target.Offset(0, -1).Value
If d.Exists(x) Then
t = d(x).Keys
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=IIf(UBound(t) <> -1, Join(t, ","), t)
End With
Else
Target = ""
End If
End If
End Sub
|
|