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