Selected Answer
Maklil
Your code used the old Evaluate function and I could not get it to work with dates (annoyingly!)
Instead I created this "universal" filter code (in the Userform1 code sheet of the attached modified file), commented for your understanding:.
Revision #1 26 February 2024- revised file attached. Includes revised code (below) to remove decimal places in Listbox results and remove negative signs from Credit values. Test removed from the TextBox1 code so that Listbox is revised if an input is deleted (also TextBox1 input is now case-insensitive).
Revision #2 27 February 2024- The code is now changed to add a variable RunTotl so that column 8 of Listbox1 now shows a "RUNNING TOTAL" of the filtered data i.e. how the debits and credits accumulate down the filtered data. (Previously it showed a "NETT MOVEMENT" for that row, which was not what the user wanted). Changes are in bold below:
Private Sub FilterLB()
Dim i As Long, x As Long
Dim AddFlag As Boolean, RunTotl As Double
On Error Resume Next
' reset results
With Me.ListBox1
.Clear
.ColumnCount = 8
.ColumnWidths = "80;90;90;80;70;70;70;90"
.AddItem
.List(0, 0) = "DATE"
.List(0, 1) = "INVOICE NO"
.List(0, 2) = "NAME"
.List(0, 3) = "CLIENT NO"
.List(0, 4) = "PAID"
.List(0, 5) = "DEBIT"
.List(0, 6) = "CREDIT"
.List(0, 7) = "RUNNING TOTAL"
End With
'set values to 0
TextBox2.Value = 0
TextBox3.Value = 0
TextBox4.Value = 0
RunTotl = 0
With Sheets("data")
For i = 2 To .Cells(1).CurrentRegion.Rows.Count
' assume this row is added
AddFlag = True
'... unless it passes any of these negative tests
If TextBox1.Text <> "" And UCase(.Cells(i, 3).Value) <> UCase(TextBox1.Text) Then AddFlag = False
If IsDate(TextBox5.Value) Then
If .Cells(i, 1).Value < CDate(TextBox5.Value) Then AddFlag = False
End If
If IsDate(TextBox6.Value) Then
If .Cells(i, 1).Value > CDate(TextBox6.Value) Then AddFlag = False
End If
If AddFlag = True Then
Me.ListBox1.AddItem
x = Me.ListBox1.ListCount - 1
' add values from row
Me.ListBox1.List(x, 0) = Format(.Cells(i, 1).Value, "dd/mm/yyyy")
Me.ListBox1.List(x, 1) = .Cells(i, 2).Value
Me.ListBox1.List(x, 2) = .Cells(i, 3).Value
Me.ListBox1.List(x, 3) = .Cells(i, 4).Value
Me.ListBox1.List(x, 4) = .Cells(i, 5).Value
Me.ListBox1.List(x, 5) = Format(.Cells(i, 6).Value, "#,#;-#,#;0")
Me.ListBox1.List(x, 6) = Format(.Cells(i, 7).Value, "#,#;-#,#;0")
' calculate the running total, starting with 0
RunTotl = RunTotl + .Cells(i, 6).Value - .Cells(i, 7).Value
Me.ListBox1.List(x, 7) = Format(RunTotl, "#,#;-#,#;0")
Me.TextBox2.Value = TextBox2.Value + .Cells(i, 6).Value
Me.TextBox3.Value = TextBox3.Value + .Cells(i, 7).Value
End If
Next i
End With
'format textboxes
Me.TextBox2.Value = Format(TextBox2.Value, "#,#.00;-#,#.00;0")
Me.TextBox3.Value = Format(TextBox3.Value, "#,#.00;-#,#.00;0")
Me.TextBox4.Value = Format(CDec(TextBox2.Value) - CDec(TextBox3.Value), "#,#.00;-#,#.00;0")
End Sub
This now puts a header row in the ListBox (DATE, INVOICE NO etc.) and is called by all modules as follows (some with tests on the input- which should be added using the Enter key)::
Option Explicit
Private Sub UserForm_Initialize()
Call FilterLB
End Sub
Private Sub TextBox1_AfterUpdate()
' refilter list box (also when name is removed)
Call FilterLB
End Sub
Private Sub TextBox5_AfterUpdate()
If TextBox5.Value = "" Then Exit Sub
If Not IsDate(TextBox5.Value) Then
TextBox5.BackColor = vbRed
Exit Sub
End If
TextBox5.BackColor = vbWhite
' refilter list box
Call FilterLB
End Sub
Private Sub TextBox6_AfterUpdate()
If TextBox6.Value = "" Then Exit Sub
If Not IsDate(TextBox6.Value) Then
TextBox6.BackColor = vbRed
Exit Sub
End If
TextBox6.BackColor = vbWhite
' refilter list box
Call FilterLB
End Sub
You can now add/remove names and/or dates and get a filtered output- which may be blank! If you add something that is not a date (in Textbox 5 or 6), it goes red.
Hope this makes sense and fixes your problem. If so, please mark this Answer as Selected.
