need new procedure in listbox on userform for average prices

[abdo meghari]

Hi,
I will add TB6,TB7 when writing dates to search data within showed in listbox ,
also when run the form should merge data on form for PURCHASE ,SALES sheets.
brings just BRAND from PURCHASE sheet based on BRANDS are existed in SALES sheet , ignore new brands in PURCHASE sheet.
so should be as the picture

should merge QTY based on BRAND column for SALES sheet, as SALES header will sum TOTAL column and divide on QTY sum
ex: BS 1200R20 G580 JAP =81600/40=2040
as PURCHASE header =100500/50=2010
as formula in TOTAL column=(2040-2010)*40
add sum word in first column and sum columns 3:5 as show in listbox.
I look forward if there is way to bold fonts for headers and SUM row in last row in listbox
the data in sheets.

populating data on form.xlsm
	A	B	C	D	E	F	G
1	DATE	CUTOMER	ID	BRAND	QTY	UNIT PRICE	TOTAL
2	01/01/2023	CRR-100	BSJ100	BS 1200R20 G580 JAP	20.00	2,000.00	40,000.00
3	02/01/2023	CRR-101	BSJ101	BS 750R16 R230 JAP	10.00	715.00	7,150.00
4	03/01/2023	CRR-102	BSJ102	BS 750R16 VSJ JAP	20.00	730.00	14,600.00
5	04/01/2023	CRR-101	BSJ100	BS 1200R20 G580 JAP	20.00	2,035.00	40,700.00
6	05/01/2023	CRR-101	BSJ102	BS 750R16 VSJ JAP	10.00	710.00	7,100.00
7	06/01/2023	CRR-100	BSJ100	BS 1200R20 G580 JAP	10.00	1,980.00	19,800.00
8	07/01/2023	CRR-103	BSJ103	BS 1200R20 G580 THI	50.00	2,025.00	101,250.00
9	08/01/2023	CRR-104	BSJ104	BS 1200R20 R187 JAP	10.00	1,980.00	19,800.00
10	09/01/2023	CRR-105	BSJ105	BS 205/70R15C R623	50.00	400.00	20,000.00
11	10/01/2023	CRR-105	BSJ103	BS 1200R20 G580 THI	40.00	2,000.00	80,000.00
12	11/01/2023	CRR-104	BSJ105	BS 205/70R15C R623	15.00	430.00	6,450.00
13	12/01/2023	CRR-101	BSJ105	BS 205/70R15C R623	20.00	440.00	8,800.00
PURCHASE

Cell Formulas
Range	Formula
G2:G13	G2	=E2*F2

populating data on form.xlsm
	A	B	C	D	E	F	G
1	DATE	CUTOMER	ID	BRAND	QTY	UNIT PRICE	TOTAL
2	01/02/2023	SRR-100	BSJ100	BS 1200R20 G580 JAP	20.00	2,000.00	40,000.00
3	02/02/2023	SRR-100	BSJ105	BS 205/70R15C R623	5.00	460.00	2,300.00
4	03/02/2023	SRR-101	BSJ105	BS 205/70R15C R623	5.00	465.00	2,325.00
5	04/02/2023	SRR-101	BSJ101	BS 750R16 R230 JAP	2.00	740.00	1,480.00
6	05/02/2023	SRR-102	BSJ101	BS 750R16 R230 JAP	2.00	755.00	1,510.00
7	05/02/2023	SRR-103	BSJ101	BS 750R16 R230 JAP	2.00	760.00	1,520.00
8	06/02/2023	SRR-100	BSJ100	BS 1200R20 G580 JAP	20.00	2,080.00	41,600.00
SALES

Cell Formulas
Range	Formula
G2:G8	G2	=E2*F2

without effect the others procedures
based on the others procedures will select option button based on sheet name then will show data for sheet name and search in textbox2 for brand to merge QTY and calculate price in others textboxs , so should keep theses procedures without any change when add new procedure as in above procedure
here is the procedures

Option Explicit
Dim a As Variant


Sub FilterData()
  Dim txt1 As String
    Dim Tot4 As Double, Tot5 As Double, Tot6 As Double        '++

  Dim i As Long, j As Long, k As Long
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    If TextBox2 = "" Then txt1 = a(i, 4) Else txt1 = TextBox2
  
    If LCase(a(i, 4)) Like LCase(txt1) & "*" Then
  
    
      k = k + 1
      For j = 1 To 7
      If j = 1 Then 'Change the 1 in this line for the column number where you have the date
          b(k, j) = Format(a(i, j), "dd/mm/yyyy")
       
        
        
        Else
          b(k, j) = a(i, j)
        If k = 1 And j = 3 Then Me.TextBox1.Value = a(i, j)       '+++

        End If
      
      Next
    End If
  Next
  If k > 0 Then ListBox1.List = b
  With ListBox1
  .ColumnWidths = "80;80;80;180;100;80;80"
 
       For i = 0 To .ListCount - 1
            Tot4 = Tot4 + .List(i, 4)                       '++
            .List(i, 4) = Format(.List(i, 4), "#,##0.00")
            Tot5 = Tot5 + .List(i, 5)                       '++
            .List(i, 5) = Format(.List(i, 5), "#,##0.00")
            Tot6 = Tot6 + .List(i, 6)                       '++
            .List(i, 6) = Format(.List(i, 6), "#,##0.00")
        Next i
  End With
 Me.TextBox3.Value = Format(Tot4, "#,##0.00")
  If Tot4 > 0 Then
    Me.TextBox4.Value = Format(Tot6 / Tot4, "#,##0.00")
  Else
    Me.TextBox4.Text = ""
  End If
  Me.TextBox5.Value = Tot6
  
 
 
 
End Sub


Private Sub OptionButton1_Click()
    If OptionButton1.Value = True And OptionButton2.Value = False Then sheet5.Select
    Call ChangeSheet
    Call FilterData
End Sub

Private Sub OptionButton2_Click()
    If OptionButton2.Value = True And OptionButton1.Value = False Then sheet6.Select
    Call ChangeSheet
    Call FilterData
End Sub

Private Sub TextBox2_Change()
Call FilterData
End Sub



Private Sub ChangeSheet()
    With ActiveSheet
        a = .Range("A2:G" & .Range("D" & Rows.Count).End(3).Row).Value
    End With
End Sub

I hope to getting chance to complete this project.