Dim f, choix(), Rng, BD(), Ncol, ColVisu()
Private Sub UserForm_Initialize()
Set f = Sheets("THESES")
ColVisu = Array(1, 2, 3, 4, 5, 6) ' colonnes à visualiser
colInterro = Array(1, 2, 3, 4, 5) ' colonnes à interroger
Set Rng = f.Range("A2:F" & f.[a65000].End(xlUp).Row)
BD = Rng.Value
Ncol = UBound(ColVisu) + 1
'-- en têtes de colonne ListBox
x = Me.ListBox1.Left + 8
Y = Me.ListBox1.Top - 12
For Each k In ColVisu
Set Lab = Me.Controls.Add("Forms.Label.1")
Lab.Caption = f.Cells(1, k)
Lab.Top = Y
Lab.Left = x
x = x + f.Columns(k).Width * 0.9
temp = temp & f.Columns(k).Width * 0.9 & ";"
Next
temp = Left(temp, Len(temp) - 1)
Me.ListBox1.ColumnCount = UBound(ColVisu) + 1
Me.ListBox1.ColumnWidths = temp
'---
ReDim choix(1 To UBound(BD))
For i = LBound(BD) To UBound(BD)
For Each k In colInterro
choix(i) = choix(i) & BD(i, k) & "|"
Next k
Next i
'--- valeurs initiales dans ListBox
Dim Tbl(): ReDim Tbl(1 To UBound(BD), 1 To Ncol)
For i = 1 To UBound(BD)
C = 0
For Each k In ColVisu
C = C + 1: Tbl(i, C) = BD(i, k)
Next k
Next i
'TriMultiCol Tbl, LBound(Tbl), UBound(Tbl), 1
Me.ListBox1.List = Tbl
Me.Label1.Caption = Me.ListBox1.ListCount & " Ligne(s)"
End Sub
Private Sub TextBox1_Change()
If Me.TextBox1 <> "" Then
mots = Split(Trim(Me.TextBox1), " ")
Tbl = choix
For i = LBound(mots) To UBound(mots)
Tbl = Filter(Tbl, mots(i), True, vbTextCompare)
Next i
If UBound(Tbl) > -1 Then
Dim b(): ReDim b(1 To UBound(Tbl) + 1, 1 To Ncol)
For i = LBound(Tbl) To UBound(Tbl)
a = Split(Tbl(i), "|")
For k = 1 To Ncol: b(i + 1, k) = a(k - 1): Next k
Next i
Me.ListBox1.List = b
Me.Label1.Caption = UBound(Tbl) + 1 & " Ligne(s)"
Else
Me.ListBox1.Clear
End If
Else
UserForm_Initialize
End If
End Sub