vba与数据有效性的例子

If Target.Count > 2 Then Exit Sub

If Target.Column <> 3 Or Target.Row <3 Then Exit Sub

Dim aa, Arr, i&

Target.Offset(0, -1) = ""

Arr = Sheet1.[a1].CurrentRegion

For i = 3 To UBound(Arr)

If Arr(i, 2) = Target.Value Then

aa = aa & Arr(i, 1) & ","

End If

Next

If aa = "" Then MsgBox "没有找到编号": Exit Sub

aa = Left(aa, Len(aa) - 1)

With Target.Offset(0, -1).Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

Operator:=xlBetween, Formula1:=aa

End With