|
Private Sub Worksheet_Change(ByVal T As Range)
If T.Row > 1 And T.Column = 3 Then
If T.Count > 1 Then End
If T.Value = "" Then End
Dim d As Object, dc As Object
Set d = CreateObject("scripting.dictionary")
With Sheets("项目明细")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 2 Then MsgBox "项目明细为空!": End
ar = .Range("a1:a" & r)
End With
zd = T.Value
For i = 2 To UBound(ar)
If InStr(ar(i, 1), zd) > 0 Then
d(ar(i, 1)) = ""
End If
Next i
T.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(d.keys, ",")
End With
T.Select
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal T As Range)
If T.Row > 1 And T.Column = 3 Then
T.Validation.Delete
End If
End Sub
|
|