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
| Private Sub ComboBox1_Change()
Me.ListBox1.Clear
For Each cel In pl
If CStr(cel.Value) = CStr(Me.ComboBox1.Value) Then
nl = cel.Row
Me.ListBox1.AddItem Sheets("Feuil1").Cells(cel.Row, 1)
With Me.ListBox1
.List(.ListCount - 1, 1) = Sheets("Feuil1").Cells(cel.Row, 2)
.List(.ListCount - 1, 2) = Sheets("Feuil1").Cells(cel.Row, 3)
.List(.ListCount - 1, 3) = Sheets("Feuil1").Cells(cel.Row, 4)
.List(.ListCount - 1, 4) = Sheets("Feuil1").Cells(cel.Row, 5)
.List(.ListCount - 1, 5) = Sheets("Feuil1").Cells(cel.Row, 6)
.List(.ListCount - 1, 6) = Sheets("Feuil1").Cells(cel.Row, 7)
.List(.ListCount - 1, 7) = Sheets("Feuil1").Cells(cel.Row, 8)
.List(.ListCount - 1, 8) = Sheets("Feuil1").Cells(cel.Row, 9)
.List(.ListCount - 1, 9) = nl
End With
End If
Next cel
If Me.ListBox1.ListCount = 1 Then Me.ListBox1.ListIndex = 0
End Sub
Private Sub ListBox1_Click()
For x = 0 To 8
Me.Controls("TextBox" & x + 1).Value = Me.ListBox1.Column(x, Me.ListBox1.ListIndex)
Next x
nl = Me.ListBox1.Column(9, Me.ListBox1.ListIndex)
TextBox10 = Range("J" & nl)
With Me.TextBox1
.SetFocus
.SelStart = 0
.SelLength = Len(.Value)
End With
End Sub |
Partager