Private Sub Worksheet_Change(ByVal Target As Range)
Dim derlig&, codbarre, codPFVA, Moncodebarre, i&, temp$, liste
Dim present As Boolean, aucun As Boolean
If Target.Address = "$B$2" And Target.Count = 1 Then
With Sheets("BaseCos")
derlig = .Cells(.Rows.Count, "a").End(xlUp).Row
codbarre = .Range(.Cells(2, "a"), .Cells(derlig, "a")).Value
codPFVA = .Range(.Cells(2, "e"), .Cells(derlig, "e")).Value
End With
Application.EnableEvents = False
Moncodebarre = Target.Value
Target.Offset(, 1).Validation.Delete
Target.Offset(, 1).ClearContents
derlig = derlig - 1
If Len(Moncodebarre) > 0 Then
For i = 1 To derlig
If codbarre(i, 1) = Moncodebarre Then
present = True
If Len(codPFVA(i, 1)) > 0 Then temp = temp & codPFVA(i, 1) & ","
End If
Next i
If Len(temp) = 0 Then aucun = True
If Not present Then
MsgBox "Code Barre absent de BasCos."
Target = Moncodebarre & vbLf & "absent de BasCos"
Application.EnableEvents = True
Exit Sub
End If
If aucun Then
MsgBox "Code Barre présent dans BasCos mais aucun code PFVA non vide correspondant."
Application.EnableEvents = True
Exit Sub
End If
If Right(temp, 1) = "," Then temp = Left(temp, Len(temp) - 1)
Target.Offset(, 1).Validation.Add xlValidateList, Formula1:=temp
liste = Split(temp, ",")
Target.Offset(, 1) = liste(0)
If UBound(liste) > 0 Then
Target.Offset(, 1).Select
SendKeys "%{down}"
End If
End If
End If
Application.EnableEvents = True
End Sub