Create accounts by user form to show balance by calculation for two columns

[abdo meghari]

Hello
I need populate data for the customer name when select combobox1 and matched with CUSTOMERS column name across sheets,also populate based on two dates in textbox1,2 when select customer combobox1

DECREASE.xlsm
	B	C	D	E	F
1	DATE	CUSTOMERS	DEBIT	CREDIT	BALANCE
2	30/08/2024	CCF-1000	1,500.00	2,000.00	-500.00
3	30/08/2024	CCF-1001	2,000.00		2,000.00
4	30/08/2024	CCF-1002	3,000.00	3,000.00	0.00
5	30/08/2024	CCF-1003	2,000.00	1,000.00	1,000.00
6	30/08/2024	CCF-1004	2,000.00		2,000.00
7	30/08/2024	CCF-1005		1,000.00	-1,000.00
8	30/08/2024	CCF-1006		1,000.00	-1,000.00
9	30/08/2024	CCF-1007	4,000.00		4,000.00
10	30/08/2024	CCF-1008	8,000.00		8,000.00
11	30/08/2024	CCF-1009	5,000.00		5,000.00
BALANCES

Cell Formulas
Range	Formula
F2:F11	F2	=D2-E2

DECREASE.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	DATE	CUSTOMERS	INV.NO	CASE	BRAND	TYPE	ORIGIN	QTY	PRICE	BALANCE
2	1	15/06/2023	CCF-1000	BSTR_23448	OUTSANDING	BS 750R16	R230	JAP	3.00	500.00	1,500.00
3	2	15/06/2023	CCF-1000	BSTR_23448	OUTSANDING	BS 700R16	R230	JAP	2.00	400.00	800.00
4	SUM										2,300.00
5	1	15/09/2023	CCF-1000	BSTR_23449	OUTSANDING	GO 1200R20	AZ0026	CHI	1.00	920.00	920.00
6	2	15/09/2023	CCF-1000	BSTR_23449	OUTSANDING	GO 1200R20	AZ0083	CHI	1.00	1,000.00	1,000.00
7	SUM										1,920.00
8	1	15/09/2023	CCF-1000	BSTR_23450	PAID	BS 1200R20	G580	JAP	9.00	1,800.00	16,200.00
9	2	15/09/2023	CCF-1000	BSTR_23450	PAID	BS 1200R20	G580	THI	9.00	1,800.00	16,200.00
10	3	15/09/2023	CCF-1000	BSTR_23450	PAID	BS 1200R20	R187	THI	10.00	1,800.00	18,000.00
11	SUM										50,400.00
12	1	16/09/2023	CCF-1001	BSTR_23452	PAID	BS 1200R20	G580	JAP	4.00	1,800.00	7,200.00
13	SUM										7,200.00
14	1	16/09/2023		BSTR_23453	OUTSANDING	BS 1200R20	G580	JAP	3.00	1,880.00	5,640.00
15	SUM		CCF-1001								5,640.00
SV

DECREASE.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	DATE	CUSTOMERS	INV.NO	CASE	BRAND	TYPE	ORIGIN	QTY	PRICE	BALANCE
2	1	15/06/2023	CCF-1000	BSJ_23444	OUTSANDING	BS 215/60R16	ER30	JAP	4.00	430.00	1,720.00
3	SUM		CCF-1000		OUTSANDING						1,720.00
4	1	15/06/2023		BSJ_23445		GO 1200R20	AZ0026	CHI	2.00	955.00	1,910.00
5	SUM										1,910.00
6	1	15/09/2023	CCF-1000	BSJ_23446	PAID	GO 1200R20	AZ0026	CHI	2.00	950.00	1,900.00
7	2	15/09/2023	CCF-1000	BSJ_23446	PAID	GO 1200R21	AZ0027	CHI	3.00	1,000.00	3,000.00
8	SUM										4,900.00
9	1	15/09/2023	CCF-1002	BSJ_23447	PAID	BS 1200R20	G580	JAP	1.00	2,000.00	2,000.00
10	2	15/09/2023	CCF-1002	BSJ_23447	PAID	BS 1200R20	G580	THI	1.00	2,000.00	2,000.00
11	3	15/09/2023	CCF-1002	BSJ_23447	PAID	BS 1200R20	R187	THI	1.00	2,000.00	2,000.00
12	SUM										6,000.00
SR

DECREASE.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	DATE	CUSTOMERS	INV.NO	CASE	BRAND	TYPE	ORIGIN	QTY	PRICE	BALANCE
2	1	15/06/2023	CCF-1000	VSTR_23444	PAID	BS 750R16	R230	JAP	1.00	500.00	500.00
3	SUM										500.00
4	1	15/09/2023	CCF-1001	VSTR_23445	OUTSANDING	GO 1200R20	AZ0083	CHI	1.00	1,000.00	1,000.00
5	SUM										1,000.00
6	1	15/09/2023	CCF-1000	VSTR_23446	OUTSANDING	BS 1200R20	G580	JAP	1.00	1,800.00	1,800.00
7	2	15/09/2023	CCF-1000	VSTR_23446	OUTSANDING	BS 1200R20	G580	THI	1.00	1,800.00	1,800.00
8	SUM										3,600.00
9	1	16/09/2023	CCF-1003	VSTR_23447	PAID	BS 215/60R16	ER30	JAP	4.00	400.00	1,600.00
10	SUM										1,600.00
11	1	16/09/2023	CCF-1002	VSTR_23448	PAID	BS 1200R20	G580	JAP	4.00	1,800.00	7,200.00
12	SUM										7,200.00
13	1	16/09/2023	CCF-1000	VSTR_23449	PAID	BS 1200R20	G580	JAP	2.00	1,880.00	3,760.00
14	SUM										3,760.00
VS

DECREASE.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	DATE	CUSTOMERS	INV.NO		BRAND	TYPE	ORIGIN	QTY	PRICE	BALANCE
2	1	10/06/2023	CCF-1000	RSS_23222	OUTSANDING	BS 215/60R16	ER30	JAP	2.00	430.00	860.00
3	SUM										860.00
4	1	10/06/2023	CCF-1004	BSJ_23445	OUTSANDING	GO 1200R20	AZ0026	CHI	1.00	955.00	955.00
5	SUM										955.00
6	1	15/09/2023	CCF-1000	BSJ_23446	PAID	GO 1200R20	AZ0026	CHI	1.00	950.00	950.00
7	2	15/09/2023	CCF-1000	BSJ_23446	PAID	GO 1200R21	AZ0027	CHI	2.00	1,000.00	2,000.00
8	SUM										2,950.00
RS

DECREASE.xlsm
	A	B	C	D
1	DATE	CUSTOMERS	CASE	BALANCE
2	15/06/2023	CCF-1000	RVCH20000	30,000.00
3	16/06/2023	CCF-1001	RVCH20001	25,000.00
4	17/06/2023	CCF-1000	RVCH20002	10,000.00
5	18/06/2023	CCF-1003	RVCH20003	1,200.00
6	15/09/2023	CCF-1004	VCH20004	4,000.00
7	15/09/2023	CCF-1000	VCH20005	15,000.00
8	15/09/2023	CCF-1006	VCH20006	17,000.00
9	16/09/2023	CCF-1007	VCH20007	18,000.00
10	17/09/2023	CCF-1008	VCH20008	1,200.00
11	18/09/2023	CCF-1009	VCH20009	1,300.00
12	19/09/2023	CCF-1010	RVCH 20010	1,400.00
13	20/09/2023		RVCH 20011	1,500.00
RECEIPT

when select CCF-1000 from combobox1 then should some steps:

1- brings balance in column F from sheet BALANCES as show in row2 under headers in listbox 1 if the amount is minus then should put in CREDIT column and the same amount put in BALANCE column as show in listbox1(if the he amount is positive then should put in DEBIT column and the same amount put in BALANCE column)
2- for RS sheet brings amount from SUM row in column K based on OUTSTANDING,PAID . if the OUTSTANDING word is existed in CASE column then will put amount in column CREDIT and the calculation in BALANCE will be the first balance in row2 + next row in DEBIT column - CREDIT column like this -500+0-860=1360
3- for SV sheet brings amount from SUM row in column K based on OUTSTANDING,PAID . if the OUTSTANDING word is existed in CASE column then will put amount in column CREDIT and the calculation in BALANCE will be the first balance in row3 + next row in DEBIT column - CREDIT column like this -1360+0-2300=-3660
4- for SR sheet brings amount from SUM row in column K based on OUTSTANDING,PAID . if the OUTSTANDING word is existed in CASE column then will put amount in column DEBIT and the calculation in BALANCE will be the first balance in row4+ next row in DEBIT column - CREDIT column like this -3660+1720-0=-1940
5-for VS sheet brings amount from SUM row in column K based on OUTSTANDING,PAID . if the PAID word is existed in CASE column then will put amount in column CREDIT and the calculation in BALANCE will be the first balance in row5 + next row in DEBIT column - CREDIT column like this -1940+0-500=-2440
6- for RECEIPT brings amount from BALANCE column and match PART of item "RVCH" in CASE column then will put in DEBIT column like this
-2440+30000-0=27560 another process for the same sheet will be= 27560+10000-0= 37560

7- for SV sheet brings amount from SUM row in column K based on OUTSTANDING,PAID . if the PAID word is existed in CASE column then will put amount in column DEBIT and the calculation in BALANCE will be the first balance in row8 + next row in DEBIT column - CREDIT column like this 37560+50400-0=86040

8- for SR sheet brings amount from SUM row in column K based on OUTSTANDING,PAID . if the PAID word is existed in CASE column then will put amount in column CREDIT and the calculation in BALANCE will be the first balance in row9 + next row in DEBIT column - CREDIT column like this 86040+0-4900=-81140
9-for VS sheet brings amount from SUM row in column K based on OUTSTANDING,PAID . if the OUTSTANDING word is existed in CASE column then will put amount in column DEBIT and the calculation in BALANCE will be the first balance in row10 + next row in DEBIT column - CREDIT column like this 81140+3600-0=84740

10- for RS sheet brings amount from SUM row in column K based on OUTSTANDING,PAID . if the PAID word is existed in CASE column then will put amount in column DEBIT and the calculation in BALANCE will be the first balance in row11 + next row in DEBIT column - CREDIT column like this 84740+2950-0=87690
11- for RECEIPT brings amount from BALANCE column and match PART of item "VCH" in CASE column then will put in CREDIT column like this
87690+0-15000=72690
moreover should insert SUM roe to sum column DEBIT,CREDIT and subtract column DEBIT from CREDIT and show BALANCE column .
without forgetting the data could be 9000 rows for each sheet.
here is picture to show and calculation for selected customer from combobox2

last thing I would show formatting number like"#,##0.00"
also should show - for empty digits in column DEBIT,CREDIT and if the BALANCE column is zero for any row should also show hyphen instead of zero.
thanks

 

[DanteAmor]

Well, it is a very very long request, with several details.

So I have several considerations that you should follow:

1. If you are going to capture dates, then I ask you to put a commandbutton1 in your userform, then after capturing the Customer and/or the dates you must press the button.​

​

2. In your example you have some errors, which are corrected with a macro, you will have the opportunity to verify them.​

​

3. You have dates 2023 and 2024 in your data. Just so you consider it when you do your tests.​

​

4. With the date filter, it is possible that no records exist in the "BALANCES" sheet.​

​

5. To get to your example, I had to sort the data by dates, so I need to put the data in columns P to V of the "BALANCES" sheet.​

​

6. The first time you load the data into the userform it will take a couple of seconds, as it will read all the sheets and perform the sort, after that the filters you make will be fast.​

​

7. I adjusted the size of the listbox columns and the font of the listbox, so that the amounts were aligned to the right.​

​

8. I fill the combobox with the Customers from the "BALANCES" sheet. If you already have it filled then you can delete this line from the macro.​

  ComboBox1.List = Sheets("BALANCES").Range("C2", Sheets("BALANCES").Range("C" & Rows.Count).End(3)).Value

​

Final Example:

Try:

Dim c As Variant                  '<-- At the beginning of all the code

Sub Populate_Listbox()
  Dim i As Long, j As Long, k As Long, n As Long
  Dim bal As Double, sumD As Double, sumC As Double
  Dim d As Variant, e As Variant
  Dim una As Boolean
  Dim sVal As String
  Dim ini As Double, fin As Double
  
  ListBox1.Clear
  
  'add HEADERS
  ReDim d(1 To UBound(c, 1) + 2, 1 To UBound(c, 2))
  For j = 1 To 7
    d(1, j) = c(1, j)
  Next
  d(2, 1) = 1

  'add ITEMS
  k = 1
  n = 0
  For i = 2 To UBound(c, 1)
    If c(i, 1) = ComboBox1.Value Then
    
      If TextBox1.Value = "" Then ini = c(i, 2) Else ini = CDate(TextBox1.Value)
      If TextBox2.Value = "" Then fin = c(i, 2) Else fin = CDate(TextBox2.Value)
      
      If c(i, 2) >= ini And c(i, 2) <= fin Then
        n = n + 1
        k = k + 1
        d(k, 1) = n
        For j = 2 To 7
          Select Case j
            Case 2
              d(k, j) = Format(c(i, j), "dd/mm/yyyy")
            Case 5, 6, 7
              sVal = Format(c(i, j), "#,##0.00;-#,##0.00;-")
              d(k, j) = String(15 - Len(sVal), " ") & sVal
            Case Else
              d(k, j) = c(i, j)
          End Select
        Next
        sumD = sumD + c(i, 5)
        sumC = sumC + c(i, 6)
        If una = False Then
          bal = bal + c(i, 5) - Abs(c(i, 6))
          una = True
        Else
          bal = bal + c(i, 5) - c(i, 6)
        End If
        sVal = Format(bal, "#,##0.00;-#,##0.00;-")
        d(k, 7) = String(15 - Len(sVal), " ") & sVal
        
      End If
    End If
  Next
  
  'add SUM
  k = k + 1
  d(k, 1) = "SUM"
  sVal = Format(sumD, "#,##0.00;-#,##0.00;-")
  d(k, 5) = String(15 - Len(sVal), " ") & sVal
  
  sVal = Format(sumC, "#,##0.00;-#,##0.00;-")
  d(k, 6) = String(15 - Len(sVal), " ") & sVal
  
  sVal = Format(sumD - sumC, "#,##0.00;-#,##0.00;-")
  d(k, 7) = String(15 - Len(sVal), " ") & sVal
  
  '
  ReDim e(1 To k, 1 To UBound(d, 2))
  For i = 1 To k
    For j = 1 To UBound(d, 2)
      e(i, j) = d(i, j)
    Next
  Next
  ListBox1.List = e
End Sub

Private Sub CommandButton1_Click()
  Dim bCont As Boolean
  
  If ComboBox1.ListIndex = -1 Then
    MsgBox "Selecct customer"
    Exit Sub
  End If
  
  If TextBox1.Value = "" And TextBox2.Value = "" Then
    Call Populate_Listbox
  Else
    If TextBox1.Value <> "" And TextBox2.Value = "" Then
      MsgBox "Populate To Date"
      Exit Sub
    End If
    If TextBox1.Value = "" And TextBox2.Value <> "" Then
      MsgBox "Populate From Date"
      Exit Sub
    End If
    If Not IsDate(TextBox1.Value) Or Len(TextBox1.Value) <> 10 Then
      MsgBox "From Date Invalid"
      Exit Sub
    End If
    If Not IsDate(TextBox2.Value) Or CDate(TextBox2.Value) < CDate(TextBox1.Value) Or Len(TextBox2.Value) <> 10 Then
      MsgBox "To Date Invalid"
      Exit Sub
    End If
    Call Populate_Listbox
  End If
End Sub

Private Sub UserForm_Activate()
  Dim shB As Worksheet
  Dim ba, sv, sr, vs, rs, re
  Dim i&, k&, lr&
  
  Set shB = Sheets("BALANCES")
  
  ba = shB.Range("B2:F" & shB.Range("B" & Rows.Count).End(3).Row).Value2
  sv = Sheets("SV").Range("A2:K" & Sheets("SV").Range("A" & Rows.Count).End(3).Row).Value2
  sr = Sheets("SR").Range("A2:K" & Sheets("SR").Range("A" & Rows.Count).End(3).Row).Value2
  vs = Sheets("VS").Range("A2:K" & Sheets("VS").Range("A" & Rows.Count).End(3).Row).Value2
  rs = Sheets("RS").Range("A2:K" & Sheets("RS").Range("A" & Rows.Count).End(3).Row).Value2
  re = Sheets("RECEIPT").Range("A2:D" & Sheets("RECEIPT").Range("A" & Rows.Count).End(3).Row).Value2
  
  ReDim b(1 To UBound(ba) + UBound(sv) + UBound(sr) + UBound(vs) + UBound(rs) + UBound(re), 1 To 7)
  
  Call fill_a(b, ba, k)                         'BALANCES
  Call fill_b(b, sv, k, "PAID", "OUTSANDING")   'SV
  Call fill_b(b, sr, k, "OUTSANDING", "PAID")   'SR
  Call fill_b(b, vs, k, "OUTSANDING", "PAID")   'VS
  Call fill_b(b, rs, k, "PAID", "OUTSANDING")   'RS
  Call fill_c(b, re, k)                         'RECEIPT

  Application.ScreenUpdating = False
  shB.Range("P1").Resize(1, 7).Value = Array("ITEM", "DATE", "INV.NO", "CASE", "DEBIT", "CREDIT", "BALANCE")
  shB.Range("P2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  lr = shB.Range("V" & Rows.Count).End(3).Row
  For i = 2 To lr
    If shB.Range("R" & i).Value <> "" Then
      shB.Range("P" & i & ":V" & lr).Sort shB.Range("Q" & i), xlAscending, Header:=xlNo
      Exit For
    End If
  Next
  Application.ScreenUpdating = True
  
  c = shB.Range("P1:V" & lr).Value
End Sub

Sub fill_a(b, ba, k)
  Dim i As Long
  
  For i = 1 To UBound(ba)
    k = k + 1
    b(k, 1) = ba(i, 2)          'customer
    b(k, 2) = ba(i, 1)          'date
    If ba(i, 5) > 0 Then
      b(k, 5) = ba(i, 5)        'dbt
      b(k, 6) = 0               'cdt
    Else
      b(k, 6) = ba(i, 5)        'cdt
      b(k, 5) = 0               'dbt
    End If
    b(k, 7) = ba(i, 5)          'balance
  Next
End Sub

Sub fill_c(b, re, k)
  Dim i As Long
  
  For i = 1 To UBound(re)
    k = k + 1
    b(k, 1) = re(i, 2)          'customer
    b(k, 2) = re(i, 1)          'date
    b(k, 4) = re(i, 3)          'case
    If Left(re(i, 3), 4) = "RVCH" Then
      b(k, 5) = re(i, 4)        'dbt
      b(k, 6) = 0               'cdt
    ElseIf Left(re(i, 3), 3) = "VCH" Then
      b(k, 5) = 0               'dbt
      b(k, 6) = re(i, 4)        'cdt
    End If
    b(k, 7) = re(i, 4)          'balance
  Next
End Sub

Sub fill_b(b, ary, k, c1, c2)
  Dim i As Long
  
  For i = 1 To UBound(ary)
    If ary(i, 1) = "SUM" Then
      k = k + 1
      b(k, 1) = ary(i - 1, 3)   'customer
      b(k, 2) = ary(i - 1, 2)   'date
      b(k, 3) = ary(i - 1, 4)   'inv
      b(k, 4) = ary(i - 1, 5)   'case
      If ary(i - 1, 5) = c1 Then
        b(k, 5) = ary(i, 11)    'dbt
        b(k, 6) = 0             'cdt
      ElseIf ary(i - 1, 5) = c2 Then
        b(k, 5) = 0             'dbt
        b(k, 6) = ary(i, 11)    'cdt
      End If
      b(k, 7) = ary(i, 11)      'bal
    End If
  Next
End Sub

Private Sub UserForm_Initialize()
  ComboBox1.List = Sheets("BALANCES").Range("C2", Sheets("BALANCES").Range("C" & Rows.Count).End(3)).Value
  With ListBox1
    .ColumnCount = 7
    .Font.Name = "Consolas"
    .Font.Size = 10
    .ColumnWidths = "40;60;100;100;100;100;100"
  End With
End Sub

I tested with 9 thousand records on each sheet and it works well. But you will have to do a lot of testing to verify the results. If any result is not correct, you should put the data sample here, so that I can do the test.

I hope everything is fine though.

 

[abdo meghari]

Well, it is a very very long request, with several details.

sorry !
I 'm confused how I post this big subject . I thought if I split this project into multiple threads , but chance to getting help is slight little, even I add this subject I don't except help because it's really hard to do that by form, but you did it as your usual , and I'm glad to follow my threads.

In your example you have some errors

sorry about it !
actually by yellow highlighted in picture I've found out after leave 10 minutes can't edited to correct it, and I don't want post new for this as long I clarify the details and I hoped this missed row is not obstacle to understand my requirements.
as to green highlighted item I accept your way.
I tested code with big data and based on my requirements works excellently!
but I would fix my mistake, sorry!
in first row for BALANCE column I would subtract amount DEBIT from CREDIT example:
for CCF-1000= 0-(-500)=500 because the last row should show the same balance in BALANCE column as in ITEM 14 for BALANCE column .
thanks again

 

[DanteAmor]

I tested code with big data and based on my requirements works excellently!

in first row for BALANCE column I would subtract amount DEBIT from CREDIT example:
for CCF-1000= 0-(-500)=500 because the last row should show the same balance in BALANCE column as in ITEM 14 for BALANCE column .

That took me a lot of work to make the first balance record add up differently than the others.

But don't worry, the correction is simpler, I put the corrected code here:

Dim c As Variant                  '<-- At the beginning of all the code

Sub Populate_Listbox()
  Dim i As Long, j As Long, k As Long, n As Long
  Dim bal As Double, sumD As Double, sumC As Double
  Dim d As Variant, e As Variant
  'Dim una As Boolean
  Dim sVal As String
  Dim ini As Double, fin As Double
 
  ListBox1.Clear
 
  'add HEADERS
  ReDim d(1 To UBound(c, 1) + 2, 1 To UBound(c, 2))
  For j = 1 To 7
    d(1, j) = c(1, j)
  Next
  d(2, 1) = 1

  'add ITEMS
  k = 1
  n = 0
  For i = 2 To UBound(c, 1)
    If c(i, 1) = ComboBox1.Value Then
   
      If TextBox1.Value = "" Then ini = c(i, 2) Else ini = CDate(TextBox1.Value)
      If TextBox2.Value = "" Then fin = c(i, 2) Else fin = CDate(TextBox2.Value)
     
      If c(i, 2) >= ini And c(i, 2) <= fin Then
     
        n = n + 1
        k = k + 1
        d(k, 1) = n
        For j = 2 To 7
          Select Case j
            Case 2
              d(k, j) = Format(c(i, j), "dd/mm/yyyy")
            Case 5, 6, 7
              sVal = Format(c(i, j), "#,##0.00;-#,##0.00;-")
              d(k, j) = String(15 - Len(sVal), " ") & sVal
            Case Else
              d(k, j) = c(i, j)
          End Select
        Next
        sumD = sumD + c(i, 5)
        sumC = sumC + c(i, 6)
        bal = bal + c(i, 5) - c(i, 6)
        sVal = Format(bal, "#,##0.00;-#,##0.00;-")
        d(k, 7) = String(15 - Len(sVal), " ") & sVal
       
      End If
    End If
  Next
 
  'add SUM
  k = k + 1
  d(k, 1) = "SUM"
  sVal = Format(sumD, "#,##0.00;-#,##0.00;-")
  d(k, 5) = String(15 - Len(sVal), " ") & sVal
 
  sVal = Format(sumC, "#,##0.00;-#,##0.00;-")
  d(k, 6) = String(15 - Len(sVal), " ") & sVal
 
  sVal = Format(sumD - sumC, "#,##0.00;-#,##0.00;-")
  d(k, 7) = String(15 - Len(sVal), " ") & sVal
 
  '
  ReDim e(1 To k, 1 To UBound(d, 2))
  For i = 1 To k
    For j = 1 To UBound(d, 2)
      e(i, j) = d(i, j)
    Next
  Next
  ListBox1.List = e
End Sub

Private Sub CommandButton1_Click()
  Dim bCont As Boolean
 
  If ComboBox1.ListIndex = -1 Then
    MsgBox "Selecct customer"
    Exit Sub
  End If
 
  If TextBox1.Value = "" And TextBox2.Value = "" Then
    Call Populate_Listbox
  Else
    If TextBox1.Value <> "" And TextBox2.Value = "" Then
      MsgBox "Populate To Date"
      Exit Sub
    End If
    If TextBox1.Value = "" And TextBox2.Value <> "" Then
      MsgBox "Populate From Date"
      Exit Sub
    End If
    If Not IsDate(TextBox1.Value) Or Len(TextBox1.Value) <> 10 Then
      MsgBox "From Date Invalid"
      Exit Sub
    End If
    If Not IsDate(TextBox2.Value) Or CDate(TextBox2.Value) < CDate(TextBox1.Value) Or Len(TextBox2.Value) <> 10 Then
      MsgBox "To Date Invalid"
      Exit Sub
    End If
    Call Populate_Listbox
  End If
End Sub

Private Sub UserForm_Activate()
  Dim shB As Worksheet
  Dim ba, sv, sr, vs, rs, re
  Dim i&, k&, lr&
 
  Set shB = Sheets("BALANCES")
 
  ba = shB.Range("B2:F" & shB.Range("B" & Rows.Count).End(3).Row).Value2
  sv = Sheets("SV").Range("A2:K" & Sheets("SV").Range("A" & Rows.Count).End(3).Row).Value2
  sr = Sheets("SR").Range("A2:K" & Sheets("SR").Range("A" & Rows.Count).End(3).Row).Value2
  vs = Sheets("VS").Range("A2:K" & Sheets("VS").Range("A" & Rows.Count).End(3).Row).Value2
  rs = Sheets("RS").Range("A2:K" & Sheets("RS").Range("A" & Rows.Count).End(3).Row).Value2
  re = Sheets("RECEIPT").Range("A2:D" & Sheets("RECEIPT").Range("A" & Rows.Count).End(3).Row).Value2
 
  ReDim b(1 To UBound(ba) + UBound(sv) + UBound(sr) + UBound(vs) + UBound(rs) + UBound(re), 1 To 7)
 
  Call fill_a(b, ba, k)                         'BALANCES
  Call fill_b(b, sv, k, "PAID", "OUTSANDING")   'SV
  Call fill_b(b, sr, k, "OUTSANDING", "PAID")   'SR
  Call fill_b(b, vs, k, "OUTSANDING", "PAID")   'VS
  Call fill_b(b, rs, k, "PAID", "OUTSANDING")   'RS
  Call fill_c(b, re, k)                         'RECEIPT

  Application.ScreenUpdating = False
  shB.Range("P1").Resize(1, 7).Value = Array("ITEM", "DATE", "INV.NO", "CASE", "DEBIT", "CREDIT", "BALANCE")
  shB.Range("P2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  lr = shB.Range("V" & Rows.Count).End(3).Row
  For i = 2 To lr
    If shB.Range("R" & i).Value <> "" Then
      shB.Range("P" & i & ":V" & lr).Sort shB.Range("Q" & i), xlAscending, Header:=xlNo
      Exit For
    End If
  Next
  Application.ScreenUpdating = True
 
  c = shB.Range("P1:V" & lr).Value
End Sub

Sub fill_a(b, ba, k)
  Dim i As Long
 
  For i = 1 To UBound(ba)
    k = k + 1
    b(k, 1) = ba(i, 2)          'customer
    b(k, 2) = ba(i, 1)          'date
    If ba(i, 5) > 0 Then
      b(k, 5) = ba(i, 5)        'dbt
      b(k, 6) = 0               'cdt
    Else
      b(k, 6) = ba(i, 5)        'cdt
      b(k, 5) = 0               'dbt
    End If
    b(k, 7) = ba(i, 5)          'balance
  Next
End Sub

Sub fill_c(b, re, k)
  Dim i As Long
 
  For i = 1 To UBound(re)
    k = k + 1
    b(k, 1) = re(i, 2)          'customer
    b(k, 2) = re(i, 1)          'date
    b(k, 4) = re(i, 3)          'case
    If Left(re(i, 3), 4) = "RVCH" Then
      b(k, 5) = re(i, 4)        'dbt
      b(k, 6) = 0               'cdt
    ElseIf Left(re(i, 3), 3) = "VCH" Then
      b(k, 5) = 0               'dbt
      b(k, 6) = re(i, 4)        'cdt
    End If
    b(k, 7) = re(i, 4)          'balance
  Next
End Sub

Sub fill_b(b, ary, k, c1, c2)
  Dim i As Long
 
  For i = 1 To UBound(ary)
    If ary(i, 1) = "SUM" Then
      k = k + 1
      b(k, 1) = ary(i - 1, 3)   'customer
      b(k, 2) = ary(i - 1, 2)   'date
      b(k, 3) = ary(i - 1, 4)   'inv
      b(k, 4) = ary(i - 1, 5)   'case
      If ary(i - 1, 5) = c1 Then
        b(k, 5) = ary(i, 11)    'dbt
        b(k, 6) = 0             'cdt
      ElseIf ary(i - 1, 5) = c2 Then
        b(k, 5) = 0             'dbt
        b(k, 6) = ary(i, 11)    'cdt
      End If
      b(k, 7) = ary(i, 11)      'bal
    End If
  Next
End Sub

Private Sub UserForm_Initialize()
  ComboBox1.List = Sheets("BALANCES").Range("C2", Sheets("BALANCES").Range("C" & Rows.Count).End(3)).Value
  With ListBox1
    .ColumnCount = 7
    .Font.Name = "Consolas"
    .Font.Size = 10
    .ColumnWidths = "40;60;100;100;100;100;100"
  End With
End Sub

And the image with the example:

Note: you must capture the dates in the dd/mm/yyyy format, including zeros, for example for May 2: 02/05/2024

 

[abdo meghari]

Note: you must capture the dates in the dd/mm/yyyy format, including zeros, for example for May 2: 02/05/2024

understood , based on formatting inside the code.
many thanks for this a unique help.