Hi Expert,
I found a VBA code that can show the data in Listbox when I type the data in textbox1. However, I have more than 1 dependable Textboxes. Just like the below screen:
Below are the codes:
Below are the data worksheet:
I found a VBA code that can show the data in Listbox when I type the data in textbox1. However, I have more than 1 dependable Textboxes. Just like the below screen:
Below are the codes:
VBA Code:
Private Sub txtpol_Change()
Dim ws As Worksheet
Dim i, LR As Long
Set ws = ThisWorkbook.Worksheets(1)
LR = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Me.txtpol = Format(StrConv(Me.txtpol, vbLowerCase))
Me.ListBox1.Clear
Me.ListBox1.AddItem "Title1"
Me.ListBox1.List(0, 1) = "Title2"
Me.ListBox1.List(0, 2) = "Title3"
Me.ListBox1.List(0, 3) = "Title4"
Me.ListBox1.List(0, 4) = "Title5"
Me.ListBox1.List(0, 5) = "Title6"
Me.ListBox1.List(0, 6) = "Title7"
Me.ListBox1.Selected(0) = True
For i = 11 To LR
For x = 1 To Len(ws.Cells(i, 1))
a = Me.txtbox1.TextLength
If LCase(Mid(ws.Cells(i, 1), x, a)) = Me.txtbox1 And Me.txtbox1 <> "" Then
Me.ListBox1.AddItem ws.Cells(i, 1)
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Cells(i, 2)
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = ws.Cells(i, 3)
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = ws.Cells(i, 4)
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = ws.Cells(i, 5)
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = ws.Cells(i, 6)
Me.ListBox1.List(ListBox1.ListCount - 1, 6) = ws.Cells(i, 7)
End If
Next x
Next i
End Sub
Below are the data worksheet:
Title1 | Title2 | Title3 | Title4 | Title5 | Title6 | Title7 |
HK | KW | CXXX | $ 1,087.00 | $ 3,828.00 | $ 5,143.00 | $ 8,335.00 |
CN | BJ | KHXX | $ 6,698.00 | $ 2,208.00 | $ 6,838.00 | $ 1,275.00 |
US | WS | CXXX | $ 1,947.00 | $ 1,148.00 | $ 2,000.00 | $ 6,607.00 |
AU | BA | COXX | $ 6,150.00 | $ 6,448.00 | $ 8,299.00 | $ 5,216.00 |
AD | CD | ABCC | $ 9,518.00 | $ 7,224.00 | $ 7,337.00 | $ 7,955.00 |
AZ | EF | CXXX | $ 6,854.00 | $ 5,779.00 | $ 9,959.00 | $ 3,954.00 |
AI | GH | KHXX | $ 3,250.00 | $ 6,113.00 | $ 5,750.00 | $ 4,814.00 |
FR | DK | CXXX | $ 7,210.00 | $ 3,139.00 | $ 4,598.00 | $ 5,273.00 |
BR | DG | COXX | $ 8,037.00 | $ 2,884.00 | $ 5,721.00 | $ 2,388.00 |
BV | DD | ABCC | $ 7,114.00 | $ 4,512.00 | $ 7,394.00 | $ 8,134.00 |
CV | AD | CXXX | $ 890.00 | $ 1,467.00 | $ 945.00 | $ 7,420.00 |
KH | CI | KHXX | $ 3,960.00 | $ 8,218.00 | $ 4,893.00 | $ 3,633.00 |
CM | CI | CXXX | $ 6,005.00 | $ 6,314.00 | $ 9,405.00 | $ 9,527.00 |
CA | AD | COXX | $ 3,517.00 | $ 6,655.00 | $ 8,604.00 | $ 9,522.00 |
CX | GH | ABCC | $ 1,087.00 | $ 3,828.00 | $ 5,143.00 | $ 8,335.00 |
CD | BA | CXXX | $ 1,087.00 | $ 3,828.00 | $ 5,143.00 | $ 8,335.00 |
HK | BA | KHXX | $ 1,087.00 | $ 3,828.00 | $ 5,143.00 | $ 8,335.00 |
CG | CD | CXXX | $ 1,087.00 | $ 3,828.00 | $ 5,143.00 | $ 8,335.00 |