1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
|
Private Sub UserForm_Initialize()
Dim Plage As Range
With Worksheets("Feuil1")
Set Plage = .Range(.Cells(1, 1), .Cells(.Rows.Count, 4).End(xlUp))
End With
With Plage
'début à 2 pour éviter les entêtes
ComboBox1.List = Plage.Range(.Cells(2, 1), .Cells(Plage.Rows.Count, 1)).Value
ComboBox2.List = Plage.Range(.Cells(2, 2), .Cells(Plage.Rows.Count, 2)).Value
ComboBox3.List = Plage.Range(.Cells(2, 3), .Cells(Plage.Rows.Count, 3)).Value
ComboBox4.List = Plage.Range(.Cells(2, 4), .Cells(Plage.Rows.Count, 4)).Value
End With
End Sub
Private Sub ComboBox1_Click()
RemplirCombo ComboBox1
End Sub
Private Sub ComboBox2_Click()
RemplirCombo ComboBox2
End Sub
Private Sub ComboBox3_Click()
RemplirCombo ComboBox3
End Sub
Private Sub ComboBox4_Click()
RemplirCombo ComboBox4
End Sub
Private Sub RemplirCombo(Combo As MSForms.ComboBox)
Dim Tbl() As Long
Dim I As Long
Dim J As Long
For I = 0 To Combo.ListCount - 1
If Combo.List(I) = Combo.Text Then
J = J + 1
ReDim Preserve Tbl(1 To J)
Tbl(J) = I + 2 '1 pour la ligne d'entêtes et 1 pour la base 0 de la liste des combobox
End If
Next I
ComboBox1.Clear
ComboBox2.Clear
ComboBox3.Clear
ComboBox4.Clear
With Worksheets("Feuil1")
For I = 1 To UBound(Tbl)
ComboBox1.AddItem .Cells(Tbl(I), 1).Value
ComboBox2.AddItem .Cells(Tbl(I), 2).Value
ComboBox3.AddItem .Cells(Tbl(I), 3).Value
ComboBox4.AddItem .Cells(Tbl(I), 4).Value
Next I
End With
End Sub |
Partager