Populate Listbox1 based on TextBox1 value

J

jagrenet

Board Regular
Joined
Feb 23, 2022
Messages
81
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
Platform
  1. Windows
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
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.

VBA Code: 
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

AutoPopulate ListBox.jpg

AutoPopulate ListBox-2.jpg
 
Upvote 0
Saurabhj said:
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.

VBA Code: 
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
Click to expand...

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

1646461877902.jpeg


1646461930929.jpeg
 
Upvote 0
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.

1646475565392.png



VBA 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
 
Upvote 0
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

Code: 
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
 
Upvote 0
Saurabhj said:
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


VBA 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
Click to expand...
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".
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    251.9 KB · Views: 36
Upvote 0
dmt32 said:
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

Code: 
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
Click to expand...
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 !!!
 
Upvote 0
jagrenet said:
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 !!!
Click to expand...
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
 
Upvote 0
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
 
Upvote 0
dmt32 said:
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
Click to expand...
I have the files in DropBox. I will need your email to give you access.
 
Upvote 0
You must log in or register to reply here.

Similar threads

GomaPile
  • Question Question
Listbox1 Error
Replies
4
Views
257
GomaPile
GomaPile
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
192
krunal123
K
C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
231
campbell1093
C
I
  • Question Question
Select Listbox value to be then taken to customer on worksheet
Replies
1
Views
104
ipbr21054
I
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
79
Ali M
A

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top