Dim Rng, TblBD(), NbCol
Option Compare Text
Private Sub UserForm_Initialize()
Set f = Sheets("BD")
Set Rng = f.Range("A2:K" & f.[A650000].End(xlUp).Row)
TblBD = Rng.Value
For i = 1 To UBound(TblBD):
TotalFact = TotalFact + TblBD(i, 4)
TotalCrédit = TotalCrédit + TblBD(i, 10)
TblBD(i, 4) = Format(TblBD(i, 4), "0000.00")
Next i
NbCol = UBound(TblBD, 2)
Me.ListBox1.List = TblBD
Me.TextBox2 = Format(TotalFact, "0000.00")
Me.TextBox3 = Format(TotalCrédit, "0000.00")
Me.ListBox1.ColumnCount = NbCol
'Me.ListBox1.ColumnWidths = "30;60;90;60"
EnteteListBox
End Sub
Private Sub TextBox1_Change()
colRecherche = 3
clé = "*" & Me.TextBox1 & "*"
Dim Tbl()
TotalFact = 0
For i = 1 To UBound(TblBD)
If TblBD(i, colRecherche) Like clé Then
n = n + 1
ReDim Preserve Tbl(1 To UBound(TblBD, 2), 1 To n)
For k = 1 To UBound(TblBD, 2): Tbl(k, n) = TblBD(i, k): Next k
TotalFact = TotalFact + TblBD(i, 4)
TotalCrédit = TotalCrédit + TblBD(i, 10)
End If
Next i
If n > 0 Then
Me.ListBox1.Column = Tbl
Me.TextBox2 = Format(TotalFact, "0000.00")
Me.TextBox3 = Format(TotalCrédit, "0000.00")
Else
Me.ListBox1.List = TblBD
Me.TextBox2 = Format(Application.Sum(Application.Index(TblBD, , 4)), "0000.00")
Me.TextBox3 = Format(Application.Sum(Application.Index(TblBD, , 10)), "0000.00")
End If
End Sub
Sub EnteteListBox()
x = Me.ListBox1.Left + 8
Y = Me.ListBox1.Top - 12
For i = 1 To NbCol
Set lab = Me.Controls.Add("Forms.Label.1")
lab.Caption = Rng.Offset(-1).Cells(1, i)
lab.Top = Y
lab.Left = x
x = x + Rng.Columns(i).Width * 1.1
temp = temp & Rng.Columns(i).Width * 1.1 & ";"
Next
temp = Left(temp, Len(temp) - 1)
Me.ListBox1.ColumnWidths = temp
End Sub