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
| Dim f, bd, Ncol, Rng
Option Compare Text
Private Sub UserForm_Initialize()
Set f = Sheets("bd")
Set d = CreateObject("Scripting.Dictionary")
bd = f.Range("A2:K" & f.[A65000].End(xlUp).Row).Value
Set Rng = f.Range("A2:L" & f.[A65000].End(xlUp).Row)
Ncol = Rng.Columns.Count
'alimentation des combo 1 à 3
Tri bd, LBound(bd), UBound(bd), 4 ' version tri date
d("*") = ""
For i = LBound(bd) To UBound(bd)
d(Year(bd(i, 4))) = ""
Next i
Me.ComboBox1.List = d.keys:
Me.ComboBox1 = "*"
d.RemoveAll
d("*") = ""
Tri bd, LBound(bd), UBound(bd), 2 ' version tri col B
For i = LBound(bd) To UBound(bd)
d(bd(i, 2)) = ""
Next i
Me.ComboBox2.List = d.keys
Me.ComboBox2 = "*"
d.RemoveAll
d("*") = ""
'Tri bd, LBound(bd), UBound(bd), 3 ' version tri col C
For i = LBound(bd) To UBound(bd)
d(bd(i, 3)) = ""
Next i
Me.ComboBox3.List = d.keys
Me.ComboBox3 = "*"
d.RemoveAll
Me.ListBox1.ColumnCount = 12
Me.ListBox1.ColumnWidths = "50;100;250;50;30;50;80;50;30;30;200;100"
Me.ListBox1.List = bd
End Sub
Private Sub ComboBox1_click()
affiche
End Sub
Private Sub ComboBox2_click()
affiche
End Sub
Private Sub ComboBox3_click()
affiche
End Sub
Sub affiche()
If Me.ComboBox1 = "*" Then
an = "*"
Else: an = Val(Me.ComboBox1)
ville = Me.ComboBox2
cere = Me.ComboBox3
n = 0
End If
Dim Tbl()
For i = 1 To UBound(bd)
If (Year(bd(i, 4)) = an Or an = "*") And bd(i, 2) Like ville And bd(i, 3) Like cere Then
n = n + 1:
ReDim Preserve Tbl(1 To UBound(bd, 2), 1 To n)
For K = 1 To UBound(bd, 2):
Tbl(K, n) = bd(i, K): Next K
End If
Next i
If n > 0 Then
Me.ListBox1.Column = Tbl
Else:
Me.ListBox1.Clear
End If
End Sub
Sub Tri(a, gauc, droi, colTri) ' Quick sort
ref = a((gauc + droi) \ 2, colTri)
g = gauc: d = droi
Do
Do While a(g, colTri) < ref: g = g + 1: Loop
Do While ref < a(d, colTri): d = d - 1: Loop
If g <= d Then
For c = LBound(a, 2) To UBound(a, 2)
temp = a(g, c): a(g, c) = a(d, c): a(d, c) = temp
Next
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call Tri(a, g, droi, colTri)
If gauc < d Then Call Tri(a, gauc, d, colTri)
End Sub
Private Sub ListBox1_Click()
For K = 1 To Ncol
Me("textBox" & K) = Me.ListBox1.Column(K - 1)
Next K
Me.Enreg = Me.ListBox1.Column(Ncol) + Rng.Row - 1
End Sub |
Partager