Dim f, choix1(), Tbl()
Private Sub UserForm_Initialize()
Set f = Sheets("tarif")
Tbl = f.Range("a2:h" & f.[b65000].End(xlUp).Row).Value
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(Tbl)
d(Tbl(i, 2)) = ""
Next i
choix1 = Application.Transpose(d.keys)
Tri choix1, 1, LBound(choix1), UBound(choix1)
Me.ComboBox1.List = choix1
End Sub
Private Sub ComboBox1_Change()
mots = Split(Trim(Me.ComboBox1), " ")
temp = Application.Transpose(choix1)
For i = LBound(mots) To UBound(mots)
temp = Filter(temp, mots(i), True, vbTextCompare)
Next i
Me.ComboBox1.List = temp
Me.ComboBox1.DropDown
End Sub
Private Sub ComboBox1_Click()
Set d1 = CreateObject("Scripting.Dictionary")
produit = Me.ComboBox1
For i = 1 To UBound(Tbl)
If produit = Tbl(i, 2) Then d1(Tbl(i, 3)) = ""
Next i
Me.ComboBox2.List = d1.keys
End Sub
Private Sub b_ok_Click()
ActiveCell = Me.ComboBox1
produit = Me.ComboBox1
cond = Me.ComboBox2
For i = 1 To UBound(Tbl)
If Tbl(i, 2) = produit And Tbl(i, 3) = cond Then
ActiveCell.Offset(, 2) = Tbl(i, 1)
ActiveCell.Offset(, 3) = Tbl(i, 4)
ActiveCell.Offset(, 4) = Tbl(i, 7)
End If
Next i
Unload Me
End Sub