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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
Option Compare Text
Dim TblBD(), choix(), NomTableau, NbCol
Private Sub TextBox1_keydown(ByVal keycode As MSForms.ReturnInteger, ByVal shift As Integer)
If keycode <> 13 Then Exit Sub
If Me.TextBox1 <> "" Then
'---
Set d = CreateObject("scripting.dictionary")
mots = Split(Trim(Me.TextBox1), ",")
For Each m In mots
mots2 = Split(Trim(m), " ")
Tbl = choix
For i = LBound(mots2) To UBound(mots2)
Tbl = Filter(Tbl, mots2(i), True, vbTextCompare)
Next i
For i = LBound(Tbl) To UBound(Tbl): d(Tbl(i)) = "": Next i
Next m
Tbl = d.keys
'----
n = UBound(Tbl) + 1
If n > 0 Then
ReDim TblInclude(NbCol)
ReDim Tbl2(LBound(Tbl) To n + 1, 1 To NbCol)
For j = LBound(Tbl) To UBound(Tbl)
a = Split(Tbl(j), "|")
For k = 0 To NbCol - 1
Tbl2(j, k + 1) = a(k)
If k = 2 And a(k) <> "" Then
pos = InStr(1, a(k), TextBox1.Text)
If InStr(1, a(k), TextBox1.Text & " ") Or InStr(1, a(k), TextBox1.Text & ",") Or (InStr(1, a(k), TextBox1.Text) And pos + Len(TextBox1.Text) = Len(a(k))) Then
TblInclude(j) = 1
nInclude = nInclude + 1
End If
End If
Next k
Next j
Dim Tbl3
indTab3 = 0
ReDim Tbl3(LBound(Tbl) To nInclude, 1 To NbCol)
For j = LBound(Tbl) To UBound(Tbl)
a = Split(Tbl(j), "|")
For k = 0 To NbCol - 1
If TblInclude(j) = 1 Then
Tbl3(indTab3, k + 1) = a(k)
End If
If k = NbCol - 1 And TblInclude(j) = 1 Then
indTab3 = indTab3 + 1
End If
Next
Next
Me.ListBox1.List = Tbl3
Else
Me.ListBox1.Clear
End If
Else
Me.ListBox1.List = TblBD
End If
End Sub
Private Sub UserForm_Initialize()
NomTableau = "tableau3"
TblBD = Range(NomTableau).Value
ReDim choix(1 To UBound(TblBD))
For i = LBound(TblBD) To UBound(TblBD)
NbCol = Range(NomTableau).Columns.Count
For k = 1 To NbCol: choix(i) = choix(i) & TblBD(i, k) & "|": Next k
Next i
Me.ListBox1.List = TblBD
'TblBD.Columns.AutoFit
EnteteListBox
End Sub
'###################### En cours de travaux #########################################
Sub EnteteListBox()
'Exit Sub
NbCol = Range(NomTableau).Columns.Count
x = Me.ListBox1.Left + 8
Y = Me.ListBox1.Top - 12
For i = 1 To NbCol
Set Lab = Me.Controls.Add("Forms.Label.1")
If i = 1 Then
Lab.Caption = "Part number :"
End If
If i = 2 Then
Lab.Caption = ""
End If
If i = 3 Then
Lab.Caption = "Déscription :"
'Lab.Left = x
x = x + 5
End If
If i = 5 Then
Lab.Caption = "Référence :"
x = x - 285
End If
If i = 6 Then
Lab.Caption = "Fabriquant :"
x = x + 3
End If
Lab.Top = Y
Lab.Left = x
x = x + Range(NomTableau).Columns(i).Width * 0.9
temp = temp & Range(NomTableau).Columns(i).Width * 0.9 & ";"
Next
Me.ListBox1.ColumnCount = NbCol
temp = Left(temp, Len(temp) - 1)
Me.ListBox1.ColumnWidths = temp
USF_Recherche.ListBox1.ColumnWidths = "80;0;290;0;0;110;0;80"
End Sub |
