Populate Listbox1 based on TextBox1 value

[jagrenet]

I have UserForm1 with ListBox1 and TextBox1. What I am trying to accomplish is to allow the User to search "Sheet2" to find all records that match their input and populate the ListBox1. I have seen various methods to do this and have applied a couple of these methods however, the process falls flat at the same exact point, each time the code is run. With the 2 examples I have below, everything seems to run just fine until I get to the point of typing in the Textbox. I am using the TextBox1_Change() method and am expecting the Listbox to begin showing records immediately, with even the first character being typed. Issue: When I type, the ListBox remains blank. I have implemented 2 different versions of code. Same results, at the same spot, each time. (please see code below)

VERSION 1 - Returns nothing to the ListBox.

Private Sub TextBox1_Change()

On Error Resume Next

Workbooks("SalesForce Cases_v1.4_C1_Logo_Import_Button.xlsm").Activate
Worksheets("Sheet2").Select

Dim c As Integer
Dim column_headers
column_headers = "E"
criterion = column_headers

Sheet2.Cells(2, 4) = criterion

ListBox1.RowSource = Sheet2.Cells(2, 4)



Dim r, last_row, x As Integer
Dim i As Long
last_row = Sheet2.Range("E10000" & Rows.Count).End(xlUp).Row
For r = 2 To last_row
For x = 1 To Len(Sheet2.Cells(i, 2))


a = Len(UserForm1.TextBox1.TextLength)
If UCase(Left(Sheet2.Cells(r, criterion).Value, a)) = UCase(UserForm1.TextBox1.Text) Then

With UserForm1.ListBox1

.AddItem Sheet2.Cells(r, "A").Value
.List(.ListCount - 1, 1) = Sheet2.Cells(r, "B").Value
.List(.ListCount - 1, 2) = Sheet2.Cells(r, "C").Value
.List(.ListCount - 1, 3) = Sheet2.Cells(r, "D").Value
.List(.ListCount - 1, 4) = Sheet2.Cells(r, "E").Value
.List(.ListCount - 1, 5) = Sheet2.Cells(r, "F").Value
.List(.ListCount - 1, 6) = Sheet2.Cells(r, "G").Value
.List(.ListCount - 1, 7) = Sheet2.Cells(r, "H").Value
.List(.ListCount - 1, 8) = Sheet2.Cells(r, "I").Value
.List(.ListCount - 1, 9) = Sheet2.Cells(r, "J").Value
.List(.ListCount - 1, 10) = Sheet2.Cells(r, "K").Value
.List(.ListCount - 1, 11) = Sheet2.Cells(r, "L").Value
.List(.ListCount - 1, 12) = Sheet2.Cells(r, "M").Value
.List(.ListCount - 1, 13) = Sheet2.Cells(r, "N").Value
.List(.ListCount - 1, 14) = Sheet2.Cells(r, "O").Value
.List(.ListCount - 1, 15) = Sheet2.Cells(r, "P").Value
.List(.ListCount - 1, 16) = Sheet2.Cells(r, "Q").Value
.List(.ListCount - 1, 17) = Sheet2.Cells(r, "R").Value
.List(.ListCount - 1, 18) = Sheet2.Cells(r, "S").Value
.List(.ListCount - 1, 19) = Sheet2.Cells(r, "T").Value
.List(.ListCount - 1, 20) = Sheet2.Cells(r, "U").Value
.List(.ListCount - 1, 21) = Sheet2.Cells(r, "V").Value
.List(.ListCount - 1, 22) = Sheet2.Cells(r, "W").Value
.List(.ListCount - 1, 23) = Sheet2.Cells(r, "X").Value
.List(.ListCount - 1, 24) = Sheet2.Cells(r, "Y").Value
.List(.ListCount - 1, 25) = Sheet2.Cells(r, "Z").Value
.List(.ListCount - 1, 26) = Sheet2.Cells(r, "AA").Value
.List(.ListCount - 1, 27) = Sheet2.Cells(r, "AB").Value
.List(.ListCount - 1, 28) = Sheet2.Cells(r, "AC").Value


End With
End If
Next x
Next r


End Sub

===================================================================================
VERSION 2 - Same results. Returns nothing to the ListBox.

Private Sub TextBox1_Change()

Dim i As Long
For i = 2 To Sheet2.Range("E10000").End(xlUp).Row
For x = 1 To Len(Sheet2.Cells(i, 1))


a = Me.TextBox1.TextLength
If UCase(Mid(Sheet2.Cells(i, 1), x, a)) = Me.TextBox1 And Me.TextBox1 <> "" Then

Me.ListBox1.AddItem Sheet2.Cells(i, 1)
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "0" & Sheet2.Cells(i, 2)

End If
Next x
Next i

End Sub
===================================================================================

There could potentially be some leftover code from previous attempts that simply did not get cleaned up. Please ignore.

 

[dmt32]

Hello, is it possible to execute the same hope and display the data of several specific sheets on the listbox

quite possibly but I probably would consider an alternative approach in coding

Suggest that you start your own thread which will attract fresh ideas from others here

Dave