Search within a ListBox as you type in a TextBox

[Boechat]

Greetings, this is my first post here, but you've all helped me a lot actually in other threads already.

I've been having trouble solving the following issue:

I have a userform containing a listbox and a textbox among other items. I want the text that is typed in the textbox to be searched throughout the items in the listbox. Since the items are deletable, I couldn't work with fixed ranges.

I went through all the threads I could find but still found no solution. Hope you get to help me out.

Cheers

[jaslake]

Hi Boechat

Welcome to the Forum!!!

Attached is an example of what you appear to be asking for.

http://www.excelforum.com/excel-prog...combo-box.html

[stimulus]

Depending on the number of items in your list-box, I would suggest against searching while typing text in the text box. This is because you would need to add a script in the change event of the text-box, meaning it will run every time a character it added or removed. Being that you would have to iterate through all of the items in the list to search for a match, this could be quite annoying for end users.

Better would be to add the script to either the exit event of the text-box, or attach it to a clickable button / image allowing the user to trigger the search.


Something like;

Please Login or Register  to view this content.

This will loop through the items in the list-box searching for a match in the first column, and if a match is found, it will be highlighted.

Hope this helps.

[Boechat]

Thank you for the quick reply, Jaslake!

I'm not quite sure whether I was too dull to find the specific code in the sheets you sent me or if I wasn't clear. Sorry, I'm still a rookie at this haha

However I'm attaching the codes I have for the current form. There are 2 commands to open other windows, a simple deleting command for old data and the listbox filling command. What I would like to have is my TextBox1 real time filtering all items in the listbox and displaying only the ones containing the typed info.

___________________________________________________________________________________________________
Private Sub UserForm_Activate()
Me.ListBox1.List = Sheets("Contatos").Range("A2:D500").Value
End Sub
___________________________________________________________________________________________________
Private Sub CommandButton3_Click()
Contactsframe.Hide
Mainframe.Show
End Sub
___________________________________________________________________________________________________
Private Sub CommandButton4_Click()
Contactsframe.Hide
Newcontactframe.Show
End Sub
___________________________________________________________________________________________________
Private Sub CommandButton5_Click()
ListBox1.RemoveItem (ListBox1.ListIndex)
Range("Cont").Cells(Me.ListBox1.ListIndex + 1, 1).EntireRow.Delete
End Sub
___________________________________________________________________________________________________

Where "Contatos" is the sheet containing the info for the listbox and Cont is the range defined by A2:D500.

Forgive me for the lack of skill and thanks for your support,

Boëchat

[Boechat]

Depending on the number of items in your list-box, I would suggest against searching while typing text in the text box. This is because you would need to add a script in the change event of the text-box, meaning it will run every time a character it added or removed. Being that you would have to iterate through all of the items in the list to search for a match, this could be quite annoying for end users.

Better would be to add the script to either the exit event of the text-box, or attach it to a clickable button / image allowing the user to trigger the search.


Something like;

Please Login or Register  to view this content.

This will loop through the items in the list-box searching for a match in the first column, and if a match is found, it will be highlighted.

Hope this helps.

Hey, Stimulus, thanks for the reply!

The list would never be bigger than 100 items, so I think it would be OK to use the search function... The issue is that I've been requested to program it strictly in that manner, so I don't have a lot of space for maneuvers here haha

Any ideas on how to make it work?

Thank you!

[jaslake]

Hi Boechat

If you're interested I'll look at it.

Please attach a sample workbook the Structure of which is the same as your actual workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

INCLUDE ANY CODE YOU'RE USING OR HAVE TRIED.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[Boechat]

Hello again,

thanks for your support John! I managed to achieve what I wanted using this code (just in case someone needs it in future):
______________________________________________________________________________________
Private Sub TextBox1_Change()
Dim i As Long
Dim testString As String
testString = LCase("*" & TextBox1.Text & "*")

Call FillListBoxWithAll
With ListBox1
For i = .ListCount - 1 To 0 Step -1
If (Not (LCase(.List(i, 0)) Like testString) And (Not (LCase(.List(i, 1)) Like testString))) _
And (Not (LCase(.List(i, 2)) Like testString) And (Not (LCase(.List(i, 3)) Like testString))) Then
.RemoveItem i
End If
Next i
End With

End Sub
______________________________________________________________________________________
Sub FillListBoxWithAll()
ListBox1.List = Range("A2:D500").Value
End Sub
______________________________________________________________________________________
Private Sub UserForm_Initialize()
Call FillListBoxWithAll
End Sub


Hope it helps someone sometime!

Cheers

[mikerickson]

Cross-posted http://www.mrexcel.com/forum/excel-q...r-listbox.html

Boechat, please read the link in my sig about cross posting. The rules of this forum (and most others) require that if you post your question on other forums, you post a link to the other forum. The link in my signature explains why.

Thanks.

[Boechat]

Notice taken! Sorry for the inconvenience!

Boëchat