Dim ncol% 'mémorise la variable
Private Sub UserForm_Initialize()
TextBox1_Change 'lance la macro
ListBox1.ColumnCount = ncol
ListBox1.ColumnWidths = "30;70;130;60;60;70;70;60;50;70;50"
ListBox1.ColumnHeads = True 'affiche les en-têtes
End Sub
Private Sub TextBox1_Change()
Dim f As Worksheet, i&, j%
Set f = Sheets("Filtre")
f.Cells.Clear 'RAZ
ThisWorkbook.Names.Add "TB", IIf(TextBox1 = "", "*", TextBox1) 'nom défini
With Sheets("Suivis_Facture").[A3].CurrentRegion
ncol = .Columns.Count
.Cells(2, ncol + 2) = "=ISNUMBER(SEARCH(TB,C4))" 'formule à adapter éventuellement
.AdvancedFilter xlFilterCopy, .Cells(1, ncol + 2).Resize(2), f.[A1].Resize(, ncol) 'filtre avancé
.Cells(2, ncol + 2) = ""
End With
With f.UsedRange
With .Columns(4).Resize(, 7)
.Replace ",", ".", xlPart 'convertit les textes en nombres
.NumberFormat = "#,###.00"
End With
If .Rows.Count = 1 Then
ListBox1.RowSource = ""
Else
ListBox1.RowSource = .Offset(1).Resize(.Rows.Count - 1).Address(External:=True)
End If
TextBox2 = Format(Application.Sum(.Columns(4)), "#,###.00")
End With
End Sub