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 |
Partager