populate headers in listbox on form

Hi Malkal

In the attached  revised file, the Listbox will include the header row. (Note in the "safe" sheet, I changed cell A1 to "DATE", a more meaningful header title!)

The changes in FilterData code are shown in bold in the code extract below (as are new comments):

    ' changed from "A2:", to include header row
    a = Sheets("safe").Range("A1:E" & Sheets("safe").Range("D" & Rows.Count).End(3).Row).Value

    If IsDate(TextBox4.Value) Then dateFrom = DateValue(TextBox4.Value) Else dateFrom = 0
    If IsDate(TextBox5.Value) Then dateTo = DateValue(TextBox5.Value) Else dateTo = DateSerial(9999, 1, 1)

    For i = 1 To UBound(a, 1)

        If (ComboBox2.Value = "" Or LCase(a(i, 2)) Like "*" & LCase(ComboBox2.Text) & "*") And _
           (IsDate(a(i, 1)) And a(i, 1) >= dateFrom And a(i, 1) <= dateTo) Then
            k = k + 1

            ReDim Preserve b(1 To UBound(a, 2), 1 To k)
            For j = 1 To UBound(a, 2)
                b(j, k) = Format(a(i, j), "DD/MM/YYYY")
            Next
            ' Add individual totals...
            If k <= 1 Then
                If IsNumeric(a(i, 3)) Then FTotal = a(i, 3)
                If IsNumeric(a(i, 4)) Then F2Total = a(i, 4)
                Else
                If IsNumeric(a(i, 3)) Then FTotal = FTotal + a(i, 3)
                If IsNumeric(a(i, 4)) Then F2Total = F2Total + a(i, 4)
            End If

            ' added this
            Else
            If k = 0 Then
            ' add header row without adding to Totals
                k = 1
                ReDim Preserve b(1 To UBound(a, 2), 1 To k)
                For j = 1 To UBound(a, 2)
                    b(j, k) = a(i, j)
                Next
            End If

        End If

    Next i
    ' changed from k=0
    If k = 1 Then
        Me.ListBox1.Clear

    Else
        Me.ListBox1.Column = b

    End If

    ' ...add the total row
    ListBox1.AddItem
    'If k = 1 Then
    ' pick title, dependent on any values
    If (ComboBox2 <> "" Or TextBox4 <> "" Or TextBox5 <> "") And k > 0 Then
        ListBox1.List(k, 0) = "Filtered totals"
        Else
        ListBox1.List(k, 0) = "Totals"
    End If

    ' populate columns with totals
    ListBox1.List(k, 1) = ">>>>>>>>>>>>>>>>>>>>>>>"
    ListBox1.List(k, 2) = FTotal
    ListBox1.List(k, 3) = F2Total
    ListBox1.List(k, 4) = FTotal - F2Total

 '   End If

With ListBox1
For s = 0 To .ListCount - 1
.List(s, 2) = Format(.List(s, 2), "#,##0.00")
.List(s, 3) = Format(.List(s, 3), "#,##0.00")
.List(s, 4) = Format(.List(s, 4), "#,##0.00")
Next s
End With

End Sub

Also I modified the textbox code so the Filter is only applied if there's a full date (or no date) to prevent errors with partial dates:

Private Sub TextBox4_Change()
    'Only filter if date delete or full date added
    If Len(TextBox4.Value) = 0 Or Len(TextBox4.Value) = 10 Then FilterData
End Sub

Private Sub TextBox5_Change()
    'Only filter if date delete or full date added
    If Len(TextBox5.Value) = 0 Or Len(TextBox5.Value) = 10 Then FilterData
End Sub

Hope this fixes things for you. If so, please mark my Answer as Selected.

Hello Malkal,

This worked for me:

In the FilterData macro, after the block

    If k = 0 Then
        Me.ListBox1.Clear

    Else
        Me.ListBox1.Column = b

    End If

Add in this block:

With ListBox1
    .ColumnHeads = True
    .RowSource = "A2:E11"
End With

To make this dynamic replace "E11" with "E" & LR where LR is a variable that stores the number of the last row with data.

Hope this helps.

Cheers   :-)