|
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If Target.Count <> 1 Then Exit Sub
- arr = Sheets(2).UsedRange
- Set d = CreateObject("scripting.dictionary")
- For j = 2 To UBound(arr)
- If Len(arr(j, 1)) > 0 Then
- str1 = arr(j, 1)
- End If
- d(str1) = d(str1) & "," & arr(j, 2)
- Next j
- If Target.Column = 1 Then
- If Target.Row > 2 Then
- With Target.Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=Join(d.keys, ",")
- End With
- End If
- Else
- If Target.Column = 2 Then
- If Target.Row > 2 Then
- If d.exists(Cells(Target.Row, 1).Value) Then
- With Target.Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=Mid(d(Cells(Target.Row, 1).Value), 2)
- End With
- End If
- End If
- End If
- End If
- End Sub
复制代码 |
|