populate data in listbox based on multiple textboxes instead one textbox

[abdo meghari]

hi

I need modified this code to become searching based on multiple textboxes (textbox1,2,3)= COLS( D,E,F) . currently the code works based on COL D with textbox1

Private Sub TextBox1_Change()
    Dim myArray, lr, x, i
    Dim DATA As Worksheet
    Set DATA = Worksheets("purchase")
    lr = DATA.Cells(Rows.Count, 4).End(xlUp).Row
    ListBox1.Clear
    If TextBox1.Text = "" Then Exit Sub
    myArray = DATA.Range("A2:G" & lr + 1)
    ReDim y(1 To UBound(myArray) * 7, 1 To 7)
    For i = LBound(myArray) To UBound(myArray)
     a = Len(Me.TextBox1.Text)
     For x = 1 To 7
     If Left(myArray(i, x), a) = Left(TextBox1.Text, a) Then
            rw = rw + 1
            For yy = 1 To 7
                y(rw, yy) = myArray(i, yy)
            Next yy
        End If
        Next
    Next i
    If rw > 0 Then
        ListBox1.List = y()
    End If
End Sub

any help would be appreciate

 

[DanteAmor]

currently the code works based on COL D with textbox1

That is not true, the code checks the columns from A to G and if any of them match then it adds the record to the array, but also if 1 or more columns match then it duplicates the record in the array, that is, if column B and column D match the data in textbox1, so add the same record 2 times to listbox.
Do you want the code to keep doing the same for textboxes 2 and 3?
Or do you really want textbox1 to compare with column D, 2 with E, and 3 with F?

 

[abdo meghari]

do you really want textbox1 to compare with column D, 2 with E, and 3 with F?

yes

 

[abdo meghari]

can any body help,please?

 

[DanteAmor]

You could explain it with examples.
I don't understand what data you have in the textbox and what data you have in the cells and what data will finally be in the listbox.

 

[abdo meghari]

thanks Dante for follow my thread .
the picture 1 is data in sheet purchase

search textbox.xlsm
	A	B	C	D	E	F	G
1	DATE	CLIENT NO	INVOICE NO	BRAND	TYPE	ORIGIN	BALANCE
2	1/5/2021	CUS-BS-1	INV-BS-1	1200R20	G580	JAP	60
3	1/6/2021	CUS-BS-1	INV-BS-1	1200R20	G580	THI	10
4	1/7/2021	CUS-BS-1	INV-BS-1	1200R20	R187	JAP	5
5	1/8/2021	CUS-BS-1	INV-BS-1	1200R20	R187	THI	10
6	1/9/2021	CUS-BS-2	INV-BS-2	1200R24	G580	JAP	5
7	1/10/2021	CUS-BS-2	INV-BS-2	1200R20	G580	JAP	30
8	1/11/2021	CUS-BS-2	INV-BS-2	1200R20	G580	THI	50
9	1/12/2021	CUS-BS-2	INV-BS-2	1400R20	VSJ	JAP	12
10	1/13/2021	CUS-BS-2	INV-BS-2	1400R20	R180	JAP	13
PURCHASE

and this is when fill three textboxes toghether based on COLS D,E,F

note : you see stars in the sheet . this is short wort for JAPAN I no know when use Xl2BB TOOLS shows stars

 

[DanteAmor]

Put all the code inside your user form:

Option Explicit
Dim a As Variant

Private Sub TextBox1_Change()
  Call FilterData
End Sub

Private Sub TextBox2_Change()
  Call FilterData
End Sub

Private Sub TextBox3_Change()
  Call FilterData
End Sub

Sub FilterData()
  Dim txt1 As String, txt2 As String, txt3 As String
  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 TextBox1 = "" Then txt1 = a(i, 4) Else txt1 = TextBox1
    If TextBox2 = "" Then txt2 = a(i, 5) Else txt2 = TextBox2
    If TextBox3 = "" Then txt3 = a(i, 6) Else txt3 = TextBox3
    If LCase(a(i, 4)) Like LCase(txt1) & "*" And _
       LCase(a(i, 5)) Like LCase(txt2) & "*" And _
       LCase(a(i, 6)) Like LCase(txt3) & "*" Then
      k = k + 1
      For j = 1 To 7
        b(k, j) = a(i, j)
      Next
    End If
  Next
  If k > 0 Then ListBox1.List = b
End Sub

Private Sub UserForm_Activate()
  a = Sheets("purchase").Range("A2:G" & Sheets("purchase").Range("D" & Rows.Count).End(3).Row).Value
End Sub

 

[abdo meghari]

thanks very much for your assistance

 

[DanteAmor]

Im glad to help you. Thanks for the feedback

 

[Tom.Jones]

@Dante,

Please, how can I change data format in your code (I need dd/mm/yyyy)
Thank you.