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 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
| Option Explicit
Private Sub UserForm_Initialize()
Dim LastLig As Long
Dim c As Range
With Worksheets("Listes")
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each c In .Range("A2:A" & LastLig)
'Remplissage de Liste1
If c <> "" Then
Me.liste1 = c
If Me.liste1.ListIndex = -1 Then Me.liste1.AddItem c
End If
'Remplissage de Liste2
If c.Offset(, 3) <> "" Then
Me.liste2 = c.Offset(, 3)
If Me.liste2.ListIndex = -1 Then Me.liste2.AddItem c.Offset(, 3)
End If
'Remplissage de Liste3
If c.Offset(, 4) <> "" Then
Me.liste3 = c.Offset(, 4)
If Me.liste3.ListIndex = -1 Then Me.liste3.AddItem c.Offset(, 4)
End If
'Remplissage de Liste4
If c.Offset(, 6) <> "" Then
Me.liste4 = c.Offset(, 6)
If Me.liste4.ListIndex = -1 Then Me.liste4.AddItem c.Offset(, 6)
End If
'Remplissage de Liste5
If c.Offset(, 7) <> "" Then
Me.liste5 = c.Offset(, 7)
If Me.liste5.ListIndex = -1 Then Me.liste5.AddItem c.Offset(, 7)
End If
Next c
End With
Me.liste1.ListIndex = -1
Me.liste2.ListIndex = -1
Me.liste3.ListIndex = -1
Me.liste4.ListIndex = -1
Me.liste5.ListIndex = -1
End Sub
Private Sub liste1_Change()
Dim LastLig As Long
Dim c As Range
Me.liste1_2.Clear
If Me.liste1.ListIndex > -1 Then
With Worksheets("Listes")
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each c In .Range("A2:A" & LastLig)
If c.Offset(, 1) <> "" Then
If CStr(c.Value) = Me.liste1.Value Then
Me.liste1_2 = c.Offset(, 1)
If Me.liste1_2.ListIndex = -1 Then Me.liste1_2.AddItem c.Offset(, 1)
End If
End If
Next
End With
End If
Me.liste1_2.ListIndex = -1
End Sub
Private Sub liste1_2_Change()
Dim LastLig As Long
Dim c As Range
Me.liste1_3.Clear
If Me.liste1_2.ListIndex > -1 Then
With Worksheets("Listes")
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each c In .Range("B2:B" & LastLig)
If c.Offset(, 1) <> "" Then
If CStr(c) = Me.liste1_2.Value Then
Me.liste1_3 = c.Offset(, 1)
If Me.liste1_3.ListIndex = -1 Then Me.liste1_3.AddItem c.Offset(, 1)
End If
End If
Next
End With
End If
Me.liste1_3.ListIndex = -1
End Sub
Private Sub liste3_Change()
Dim LastLig As Long
Dim c As Range
Me.liste3_2.Clear
If Me.liste3.ListIndex > -1 Then
With Worksheets("Listes")
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each c In .Range("E2:E" & LastLig)
If c.Offset(, 1) <> "" Then
If CStr(c.Value) = Me.liste3.Value Then
Me.liste3_2 = c.Offset(, 1)
If Me.liste3_2.ListIndex = -1 Then Me.liste3_2.AddItem c.Offset(, 1)
End If
End If
Next
End With
End If
Me.liste3_2.ListIndex = -1
End Sub |
Partager