HOW TO POPULATE LISTBOX BASED ON TEXTBOX KEYWORDS IN EXCEL VBA

Today We will learn how to insert listbox and how to populate data on ListBox by changing the keywords in TextBox. I always used Dim sh as Worksheet. By this I set the worksheet where we are working.  and second step is add the listbox headers through the below code

With Me.ListBox1

    .AddItem "Search"

   .List(0, 1) = "ITEM CODE"

   .List(0, 2) = "SUPPLIER"

   .List(0, 3) = "ITEM"

   .List(0, 4) = "QYT"

   .List(0, 5) = "UOM"

   .List(0, 6) = "U / PRICE"

End With

You can change as you need. Me.ListBox1.Selected(0) = True helps you to select the first row of ListBox  and third step is First you need to see your worksheet From which row to serach data and upto the last row. You can find the last row by sh.Range("F" & Rows.Count).End(xlUp).Row.  Now use Lcase or Ucase function that will convert the letter into lower or upper case and as per your inputed data in TextBox1 All Matched data will be displayed automatically in your ListBox.

You can follow the following code..

HOW TO POPULATE LISTBOX BASED ON TEXTBOX KEYWORDS

1. Private Sub TextBox1_Change()
2. Me.TextBox1.Value = LCase(Me.TextBox1)
3. Dim sh As Worksheet
4. Set sh = Sheets("Sheet1")
5. Dim i As Long
6. Dim x As Long
7. Dim p As Integer
8. Me.ListBox1.Clear
9. With Me.ListBox1
10.     .AddItem "Search"
11.    .List(0, 1) = "ITEM CODE"
12.    .List(0, 2) = "SUPPLIER"
13.    .List(0, 3) = "ITEM"
14.    .List(0, 4) = "QYT"
15.    .List(0, 5) = "UOM"
16.    .List(0, 6) = "U / PRICE"
17. End With
18. Me.ListBox1.Selected(0) = True
19. For i = 3 To sh.Range("F" & Rows.Count).End(xlUp).Row
20. For x = 1 To Len(sh.Cells(i, 6))
21. p = Me.TextBox1.TextLength
22. If LCase(Mid(sh.Cells(i, 6), x, p)) = Me.TextBox1 And Me.TextBox1 <> "" Then
23. With Me.ListBox1
24. .AddItem sh.Cells(i, 6)
25. .List(.ListCount - 1, 1) = sh.Cells(i, 3)
26. .List(.ListCount - 1, 2) = sh.Cells(i, 4)
27. .List(.ListCount - 1, 3) = sh.Cells(i, 6)
28. .List(.ListCount - 1, 4) = sh.Cells(i, 7)
29. .List(.ListCount - 1, 5) = sh.Cells(i, 8)
30. .List(.ListCount - 1, 6) = sh.Cells(i, 9)
31. End With
32. End If
33. Next x
34. Next i
35. '------------NSUTRADHAR--------------------------------
36. End Sub

                                              goto below & click on download button to download this file

For Support and Feedback

You can contact if you have any queries through contact us or nsutradhar.cob@gmail.com

Please send your valuable feedback. Thanks for visiting this blogs.

To Download this file please click the below download button & wait 10 sec, you will find download link

Download

Click on Download Button After Time is Over

Download this File