Option Compare Text
Option Explicit
Dim bd()
Dim F As Worksheet
Private Sub CommandButton1_Click()
Unload USFRECHERCHE
End Sub
Private Sub UserForm_Initialize()
Dim i, k
Dim n
Set F = Worksheets("SUIVI CHANTIER")
With Me
.ListBox1.ColumnCount = 2
.ListBox1.ColumnWidths = "60;300"
.startUpPosition = 0
.Caption = "FORMULAIRE DE RECHERCHE"
.Top = 15
.Left = 550
.Label1.Caption = "TAPEZ LE TEXTE A RECHERCHER OU *"
.Label2.Caption = "CLICK DANS LA LISTE POUR AFFICHER LE CHANTIER"
.Label3.Caption = Me.ListBox1.ListCount
bd = F.Range("C7:D" & F.[C65000].End(xlUp).Row).Value
n = 0
Dim TblDest()
For i = 1 To UBound(bd, 1)
If bd(i, 1) Like "2*" 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
End If
Next i
Me.ListBox1.Column = TblDest
End With
End Sub
Private Sub ListBox1_Click()
Dim L As Long 'on ne sait jamais
L = ListBox1.ListIndex
If L < 0 Then Exit Sub
[J8] = ListBox1.List(L, 0)
' [B2] = ListBox1.List(L, 1)
End Sub
Private Sub TextBox1_Change()
Dim i, clé, n, ncol, k
Dim Tbl()
Set F = Worksheets("SUIVI CHANTIER")
With USFRECHERCHE
clé = "*" & UCase(.TextBox1) & "*"
.Label3.Caption = "Nombre : " & .ListBox1.ListCount
'.ListBox1.Clear
n = 0: ncol = UBound(bd, 2)
For i = LBound(bd) To UBound(bd)
If UCase(bd(i, 1)) Like clé Then
n = n + 1: ReDim Preserve Tbl(1 To ncol, 1 To n)
For k = 1 To ncol: Tbl(k, n) = bd(i, k): Next
End If
Next i
If n > 0 Then
ReDim Preserve Tbl(1 To ncol, 1 To n + 1)
Me.ListBox1.List = Application.Transpose(Tbl)
Me.ListBox1.RemoveItem n
End If
End With
End Sub