|
Private Sub Worksheet_Change(ByVal T As Range)
If T.Row > 1 And T.Column = 5 Then
If T.Count > 1 Then Exit Sub
If T.Value = "" Then Exit Sub
Dim d As Object
Dim ar As Variant
Set d = CreateObject("scripting.dictionary")
ar = Sheets("商品信息").[a1].CurrentRegion
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) = Trim(T.Value) Then
d(Trim(ar(i, 2))) = ""
End If
Next i
T.Offset(, 1).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(d.keys, ",") '"=$P$2:$P$10"
End With
Set d = Nothing
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal T As Range)
If T.Row > 1 And T.Column = 5 Then
If T.Count > 1 Then Exit Sub
Dim d As Object
Dim ar As Variant
Set d = CreateObject("scripting.dictionary")
ar = Sheets("商品信息").[a1].CurrentRegion
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" Then
d(Trim(ar(i, 1))) = ""
End If
Next i
T.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(d.keys, ",") '"=$P$2:$P$10"
End With
Set d = Nothing
End If
End Sub
|
|