Private Sub ComboBox1_GotFocus(): Charge ComboBox1: End Sub
Private Sub ComboBox2_GotFocus(): Charge ComboBox2: End Sub
Private Sub ComboBox3_GotFocus(): Charge ComboBox3: End Sub
Private Sub ComboBox4_GotFocus(): Charge ComboBox4: End Sub
Private Sub ComboBox5_GotFocus(): Charge ComboBox5: End Sub
Private Sub ComboBox6_GotFocus(): Charge ComboBox6: End Sub
Private Sub ComboBox7_GotFocus(): Charge ComboBox7: End Sub
Private Sub ComboBox1_Change(): Filtre: End Sub
Private Sub ComboBox2_Change(): Filtre: End Sub
Private Sub ComboBox3_Change(): Filtre: End Sub
Private Sub ComboBox4_Change(): Filtre: End Sub
Private Sub ComboBox5_Change(): Filtre: End Sub
Private Sub ComboBox6_Change(): Filtre: End Sub
Private Sub ComboBox7_Change(): Filtre: End Sub
Sub Charge(cb As ComboBox)
Dim d As Object, c As Range, a
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
For Each c In [A2].CurrentRegion.Columns(cb.TopLeftCell.Column).Offset(1).Cells
If c.Text <> "" Then d(c.Text) = ""
Next
If d.Count = 0 Then cb.Clear: Exit Sub
a = d.keys
tri a, 0, UBound(a) 'tri alphabétique
cb.List = a
End Sub
Sub Filtre()
Application.ScreenUpdating = False
[J2] = ComboBox1: [K2] = ComboBox2: [L2] = ComboBox3: [M2] = ComboBox4: [N2] = ComboBox5: [O2] = ComboBox6: [P2] = ComboBox7
[I3] = "=SUMPRODUCT(--ISNUMBER(SEARCH(J$2:P$2,A3:G3&"" "")))=7" 'critère
[A2].CurrentRegion.AdvancedFilter xlFilterInPlace, [I2:I3]
End Sub
Sub RAZ()
ComboBox1 = "": ComboBox2 = "": ComboBox3 = "": ComboBox4 = "": ComboBox5 = "": ComboBox6 = "": ComboBox7 = ""
End Sub
Sub tri(a, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
Do While a(g) < ref: g = g + 1: Loop
Do While ref < a(d): d = d - 1: Loop
If g <= d Then
temp = a(g): a(g) = a(d): a(d) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub