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.

 

[Saurabhj]

Hi, I am not sure what is the expected output.

I used the second code (made small changes) and type the character (IN CAPS) in text field and list box is generating output.

Private Sub TextBox1_Change()

Dim i As Long, x As Long, a As String
ListBox1.Clear   [B]'Added this line.[/B]
For i = 2 To Sheet2.Cells(Rows.Count, 5).End(xlUp).Row   [B]'modified this line[/B]
    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  [B]'modified this line.[/B]
            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

 

[jagrenet]

Hi, I am not sure what is the expected output.

I used the second code (made small changes) and type the character (IN CAPS) in text field and list box is generating output.

Private Sub TextBox1_Change()

Dim i As Long, x As Long, a As String
ListBox1.Clear   [B]'Added this line.[/B]
For i = 2 To Sheet2.Cells(Rows.Count, 5).End(xlUp).Row   [B]'modified this line[/B]
    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  [B]'modified this line.[/B]
            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

View attachment 59353
View attachment 59354

Hello Saurabhj,

Thank you for taking the time to go over my code.
I have copied and pasted your corrected code into my Sub Procedure however, I continue to get the same results. Nothing is returned to the ListBox. The expected result is exactly how your listbox looks. That is exactly what I am after. Ultimately, the keyword should return every single record that matches the TextBox1.Text ..... even if there are 100 or more entries. I have attached screenshots of my UserForm as current examples. The Keyword is meant to match all rows that contain "Customer Name" or (Column "E") Sheet2. Please let me know if there is any other information you need.

Thanks again,
Jeff

 

[Saurabhj]

It seems there is issue in Textbox/ Listbox/ Sheet name. I tried using multi column list box and code is working fine.

1. Check the control names and name used in code.
2. Is your code executing when you type a character in text box. Use message box to check.
3. Is the loop executing ? Use message box to check. See below code.

Private Sub TextBox1_Change()
MsgBox "change event fired..."
Dim i As Long, x As Long, a As String
ListBox1.Clear
For i = 2 To Sheet2.Cells(Rows.Count, 5).End(xlUp).Row
MsgBox "Outer Loop...."
    For x = 1 To Len(Sheet2.Cells(i, 1))
    MsgBox "inside inner Loop...."
        a = Me.TextBox1.TextLength
        If (UCase(Mid(Sheet2.Cells(i, 1), x, a)) = Me.TextBox1) And Me.TextBox1 <> "" Then
        MsgBox "Comparing..."
            Me.ListBox1.AddItem Sheet2.Cells(i, 1)
            Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet2.Cells(i, 2)
            Me.ListBox1.List(ListBox1.ListCount - 1, 2) = Sheet2.Cells(i, 3)
            Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Sheet2.Cells(i, 4)
            Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Sheet2.Cells(i, 5)
        Exit Sub
        End If
    Next x
Next i

 

[dmt32]

Hi,
Its more helpful to forum if you post copy of your worksheet using MRExcel Addin XL2BB - Excel Range to BBCode

If you have initially populated your listbox using RowSource property then you will first need to disconnect this before you can apply values using AddItem method.
In addition, as you want to display more than 10 columns of data in your listbox, you will need to first size it otherwise Additem method likely to fail.

Try following update to your code & see if does what you want

Private Sub TextBox1_Change()
    
    Dim ws          As Worksheet
    Dim rng         As Range
    Dim r           As Long, c As Long
    Dim Search      As String
    
    Search = Me.TextBox1.Value
    
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    
    'size worksheet data range
    Set rng = ws.Range(ws.Range("A2"), ws.Range("AB" & ws.Rows.Count).End(xlUp))

    With Me.ListBox1
        'disconnect rowsource
        .RowSource = ""
        .ColumnHeads = False
        'size listbox
        .ColumnCount = rng.Columns.Count
        .List = rng.Value
        
        .Clear
        
        If Len(Search) > 0 Then
            For r = 2 To rng.Rows.Count
                If UCase(ws.Cells(r, 5).Value) Like "*" & UCase(Search) & "*" Then
                    .AddItem ws.Cells(r, 1).Value
                    For c = 1 To rng.Columns.Count - 1
                        .List(.ListCount - 1, c) = ws.Cells(r, c + 1).Text
                    Next c
                End If
            Next r
        Else
            
            're-connect rowsource to display all data with column heads
            .RowSource = rng.Address
            .ColumnHeads = True
            
        End If
    End With
End Sub

Dave

 

[jagrenet]

It seems there is issue in Textbox/ Listbox/ Sheet name. I tried using multi column list box and code is working fine.

1. Check the control names and name used in code.
2. Is your code executing when you type a character in text box. Use message box to check.
3. Is the loop executing ? Use message box to check. See below code.

View attachment 59361

Private Sub TextBox1_Change()
MsgBox "change event fired..."
Dim i As Long, x As Long, a As String
ListBox1.Clear
For i = 2 To Sheet2.Cells(Rows.Count, 5).End(xlUp).Row
MsgBox "Outer Loop...."
    For x = 1 To Len(Sheet2.Cells(i, 1))
    MsgBox "inside inner Loop...."
        a = Me.TextBox1.TextLength
        If (UCase(Mid(Sheet2.Cells(i, 1), x, a)) = Me.TextBox1) And Me.TextBox1 <> "" Then
        MsgBox "Comparing..."
            Me.ListBox1.AddItem Sheet2.Cells(i, 1)
            Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet2.Cells(i, 2)
            Me.ListBox1.List(ListBox1.ListCount - 1, 2) = Sheet2.Cells(i, 3)
            Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Sheet2.Cells(i, 4)
            Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Sheet2.Cells(i, 5)
        Exit Sub
        End If
    Next x
Next i

I have indeed verified all naming conventions as you suggested.
I then copied your code with the MsgBox statements. MsgBox "change event fired..." worked just fine so, I commented that portion out and ran it again. I do NOT get ..... MsgBox "Outer Loop...." so, I surmise that is where the program is stopping. Using my own MsgBox prompt, I tested the code that comes from the Module that is connected to the "Advanced Search" button which is on Sheet1 -
(This is an excerpt off thhe code from Module1. All of this works very well.)
Dim arrItems()
ReDim arrItems(0 To UserForm1.ListBox1.ColumnCount - 1)
For j = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(j) Then

For i = 0 To UserForm1.ListBox1.ColumnCount - 1
arrItems(i) = UserForm1.ListBox1.Column(i, j)
Next i

With Sheets("Sheet2")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, UserForm1.ListBox1.ColumnCount).Value = arrItems

End With
End If
Next j
Worksheets("Sheet2").Range("A1", "AB1").EntireColumn.HorizontalAlignment = xlCenter
Worksheets("Sheet2").Range("E1").EntireColumn.HorizontalAlignment = xlLeft
Worksheets("Sheet2").Range("I1").EntireColumn.HorizontalAlignment = xlLeft
Worksheets("Sheet2").Range("A1", "AB1").EntireColumn.VerticalAlignment = xlBottom
Worksheets("Sheet2").Cells(1, 5).Select
Worksheets("Sheet2").Cells(1, 5).Value = "Customer Name"
Worksheets("Sheet2").Range("E1").EntireColumn.Select

Workbooks("Venture Tech 911 Report.xlsx").Close
UserForm1.ListBox1.RowSource = ""
UserForm1.CommandButton1.Visible = False

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

UserForm1.Show
End Sub

ListBox1 is still not being populated. Very strange behavior, ..... it should just work. I feel it is something very simple that I am overlooking, I am just unable to find it. Again, it seems to be stopping at .... rather, after "ListBox1.Clear".

 

[jagrenet]

Hi,
Its more helpful to forum if you post copy of your worksheet using MRExcel Addin XL2BB - Excel Range to BBCode

If you have initially populated your listbox using RowSource property then you will first need to disconnect this before you can apply values using AddItem method.
In addition, as you want to display more than 10 columns of data in your listbox, you will need to first size it otherwise Additem method likely to fail.

Try following update to your code & see if does what you want

Private Sub TextBox1_Change()
   
    Dim ws          As Worksheet
    Dim rng         As Range
    Dim r           As Long, c As Long
    Dim Search      As String
   
    Search = Me.TextBox1.Value
   
    Set ws = ThisWorkbook.Worksheets("Sheet2")
   
    'size worksheet data range
    Set rng = ws.Range(ws.Range("A2"), ws.Range("AB" & ws.Rows.Count).End(xlUp))

    With Me.ListBox1
        'disconnect rowsource
        .RowSource = ""
        .ColumnHeads = False
        'size listbox
        .ColumnCount = rng.Columns.Count
        .List = rng.Value
       
        .Clear
       
        If Len(Search) > 0 Then
            For r = 2 To rng.Rows.Count
                If UCase(ws.Cells(r, 5).Value) Like "*" & UCase(Search) & "*" Then
                    .AddItem ws.Cells(r, 1).Value
                    For c = 1 To rng.Columns.Count - 1
                        .List(.ListCount - 1, c) = ws.Cells(r, c + 1).Text
                    Next c
                End If
            Next r
        Else
           
            're-connect rowsource to display all data with column heads
            .RowSource = rng.Address
            .ColumnHeads = True
           
        End If
    End With
End Sub

Dave

Thank you for responding Dave !!
I am currently working through the original code using MsgBox for verification of each Function.
I have in fact, "disconnected" the RowSource property in a previous portion of the program using "ListBox1.RowSource = "".
That said, I have NOT re-connected RowSource once the UserForm1.Show has been triggered, ...... yet. Once I work through the verification process, I will apply your code and test from there. Being the weekend, I might not get to it right away but, at some point in the next few days - I will. I will update you as this moves along.
Thank you Dave !!!

 

[jagrenet]

Thank you for responding Dave !!
I am currently working through the original code using MsgBox for verification of each Function.
I have in fact, "disconnected" the RowSource property in a previous portion of the program using "ListBox1.RowSource = "".
That said, I have NOT re-connected RowSource once the UserForm1.Show has been triggered, ...... yet. Once I work through the verification process, I will apply your code and test from there. Being the weekend, I might not get to it right away but, at some point in the next few days - I will. I will update you as this moves along.
Thank you Dave !!!

Hello Dave,

I was able to apply your code yesterday and now it is actually populating the ListBox. However, there are a couple of little snags. 1) When I begin typing in the TextBox, it immediately stops at the first character and proceeds forward in the code, not allowing the user an opportunity to type anything else. 2) While there IS information being added to the ListBox, it always stops at the first Row and will not add anything beyond that. I step into the code and step each line so I can see that the variables are actually working and being assigned the correct values but, these 2 items are thwarting progress. To "counter" problem 1), I added a command button that calls TextBox1_Change. By doing so, I am able to type the entire word, Click CommandButton ...... and then it just "hangs", with no further action. Any ideas ?? - I wanted to mention, ...... I do have the means of offering a WebEx session so that this could be looked at in real time, giving you the chance to see the entire "picture" ..... IF that is a viable option for you. - The MRExcel Addin is an awesome tool however, with this particular project, there is a lot of sensitive information involved that I need to protect and not just plop it out on the Internet.

Thank you for your time and consideration !!
Jeff

 

[dmt32]

Hi,

Although solution not most efficient approach, from little light testing I did, code seemed to perform ok & assuming that you used code as published without changes, should do same for you

The best way for forum to assist further would be to create a copy of your workbook with dummy data (all sensitive data & logos removed) & place on a file sharing site like dropbox & provide a link to it.



Dave

 

[jagrenet]

Hi,

Although solution not most efficient approach, from little light testing I did, code seemed to perform ok & assuming that you used code as published without changes, should do same for you

The best way for forum to assist further would be to create a copy of your workbook with dummy data (all sensitive data & logos removed) & place on a file sharing site like dropbox & provide a link to it.



Dave

I have the files in DropBox. I will need your email to give you access.