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
|
Private Sub CB_Nom_Change()
Dim Plage As Range
Dim Cel As Range
Dim Tbl() As String
Dim Adr As String
Dim I As Long
Label_prenom.Caption = "Vous avez choisi le nom : " & CB_Nom.Value
'----------- Alimente la Listbox LB_prenom------------------
ReDim TB_LB_prenom(2)
Me.LB_prenom.Clear
With Worksheets("nom"): Set Plage = .Range(.Cells(2, 1), .Cells(.Rows.Count, 2).End(xlUp)): End With
Set Cel = Plage.Find(CB_Nom.Value, , xlValues, xlWhole)
If Not Cel Is Nothing Then
Adr = Cel.Address
Do
I = I + 1: ReDim Preserve Tbl(1 To 2, 1 To I)
Tbl(1, I) = Cel.Value
Tbl(2, I) = Cel.Offset(, 1).Value
Set Cel = Plage.FindNext(Cel)
Loop While Cel.Address <> Adr
End If
With Me.LB_prenom
.ColumnCount = 2
.ColumnWidths = "50;50"
.List = Application.Transpose(Tbl)
End With
'----------- Alimente la ListView LV_prenom------------------
With Me.LV_prenom
With .ColumnHeaders
.Clear
.Add , , "Nom", 80
.Add , , "Prénom", 80
End With
For I = 1 To UBound(Tbl, 2)
.ListItems.Add , , Tbl(1, I)
.ListItems(I).ListSubItems.Add , , Tbl(2, I)
Next I
.Gridlines = True
.View = lvwReport
End With
End Sub |
Partager