Dim f, TblBd
Private Sub UserForm_Initialize()
Set f = Sheets("base de données")
Set d = CreateObject("Scripting.Dictionary")
TblBd = f.Range("a2:f" & f.[A65000].End(xlUp).Row)
d("*") = ""
For i = LBound(TblBd) To UBound(TblBd)
a = Split(TblBd(i, 6), ";")
For Each c In a: d(c) = "": Next c
Next i
Me.ComboBox1.List = d.keys
Me.ComboBox2.List = Array("*", "F", "M")
Me.ComboBox1 = ActiveSheet.[A1]
ComboBox1_click
End Sub
Private Sub ComboBox1_click()
ListBox1.Clear
j = 0
For i = LBound(TblBd) To UBound(TblBd)
If (InStr(TblBd(i, 6), ComboBox1) > 0 And TblBd(i, 5) Like ComboBox2) _
Or (Me.ComboBox1 = "*" And TblBd(i, 5) Like ComboBox2) Then
Me.ListBox1.AddItem
For k = 1 To 6
ListBox1.List(j, k - 1) = TblBd(i, k)
Next k
j = j + 1
End If
Next i
End Sub
Private Sub ComboBox2_Click()
ComboBox1_click
End Sub
Private Sub b_ajout_Click()
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True Then
ref = Me.ListBox1.List(i, 0)
Set result = [b2:b1000].Find(what:=ref, lookat:=xlWhole)
If result Is Nothing Then
ligne = [b65000].End(xlUp).Row + 1
For k = 0 To 5
ActiveSheet.Cells(ligne, k + 2) = Me.ListBox1.List(i, k)
Next k
End If
End If
Next i
End Sub