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
|
Dim f, BD(), NbCol
Option Compare Text
Private Sub UserForm_Initialize()
Set f = Sheets("vehicules")
Set d = CreateObject("Scripting.Dictionary")
BD = f.Range("A3:E" & f.[A65000].End(xlUp).Row).Value
Me.ListBox1.List = BD
d("*") = ""
For i = LBound(BD) To UBound(BD)
d(BD(i, 1)) = ""
Next i
Me.ComboBox1.List = d.keys
'-- en têtes listbox
NbCol = 5
Me.ListBox1.ColumnCount = NbCol
x = Me.ListBox1.Left + 10
y = Me.ListBox1.Top - 12
For i = 1 To NbCol
Set Lab = Me.Controls.Add("Forms.Label.1")
Lab.Caption = f.Cells(2, i)
Lab.Top = y
Lab.Left = x
x = x + f.Columns(i).Width * 1#
temp = temp & f.Columns(i).Width * 1# & ";"
Next
Me.ListBox1.ColumnWidths = temp
'--
For i = 1 To NbCol
Me("label" & i) = f.Cells(2, i)
Me("textbox" & i).Width = f.Columns(i).Width * 1#
Next i
End Sub
Private Sub ComboBox1_click()
Marque = Me.ComboBox1
n = 0
Dim TblDest()
For i = 1 To UBound(BD)
If BD(i, 1) Like Marque Then
n = n + 1: ReDim Preserve TblDest(1 To UBound(BD, 2), 1 To n)
For k = 1 To UBound(BD, 2): TblDest(k, n) = BD(i, k): Next k
End If
Next i
Me.ListBox1.Column = TblDest
End Sub
Private Sub ListBox1_Click()
Marque = ListBox1: modele = ListBox1.Column(1): immatriculation = Me.ListBox1.Column(3)
For i = LBound(BD) To UBound(BD)
If BD(i, 1) = Marque And BD(i, 2) = modele And BD(i, 4) = immatriculation Then
For k = 1 To NbCol
Me("textbox" & k) = Me.ListBox1.Column(k - 1)
Next k
End If
Next i
End Sub |
Partager