Macro to filter through list of names in a listbox

[pimp_mentality]

Hey guys i would like some help fine tuning my userform.

Here is the deal. I have a UserForm with several TextBoxes and a Listbox which is populated with a list of names once the form is open. When I click on a name from the list, information related to that name populates the varied TextBoxes alphabetically.

The thing is the list has grown quite a bit now so what I would like to do is to have a textbox on top of the list box for searching purposes. the idea is as the user types a name in the searchbox it would filter through the names in the list boxes simultaneously as the user types. So in other words if the user is searching for the name Dave when the user types "D" the listbox list jumps to D then when they type "Da" it jumps to all name with starting with Da and so forth.

Now I think this is possible by way outside my novice coding skills. So any assistance would be appreciated

 

[Akuini]

Assuming the name list is in sheet1 col A, try this:

[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] TextBox1_Change[B]()[/B]
[COLOR=blue]Dim[/COLOR] dar [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR][B],[/B] vList[B],[/B] i [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
vList [B]=[/B] Sheets[B]([/B][COLOR=brown]"sheet1"[/COLOR][B]).[/B]Range[B]([/B][COLOR=brown]"A2"[/COLOR][B],[/B] Sheets[B]([/B][COLOR=brown]"sheet1"[/COLOR][B]).[/B]Cells[B]([/B]Rows.Count[B],[/B] [COLOR=brown]"A"[/COLOR][B]).[/B][COLOR=blue]End[/COLOR][B]([/B]xlUp[B])).[/B]Value
    [COLOR=blue]Set[/COLOR] dar [B]=[/B] CreateObject[B]([/B][COLOR=brown]"System.Collections.ArrayList"[/COLOR][B])[/B]
 
    [COLOR=blue]For[/COLOR] i [B]=[/B] LBound[B]([/B]vList[B])[/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]vList[B])[/B]
        [I][COLOR=seagreen]'If LCase(CStr(vList(i, 1))) Like "*" & Replace(LCase(cbox.Value), " ", "*") & "*" Then[/COLOR][/I]
        [COLOR=blue]If[/COLOR] LCase[B]([/B][COLOR=blue]CStr[/COLOR][B]([/B]vList[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])))[/B] [COLOR=blue]Like[/COLOR] Replace[B]([/B]LCase[B]([/B]TextBox1.Value[B]),[/B] [COLOR=brown]" "[/COLOR][B],[/B] [COLOR=brown]"*"[/COLOR][B])[/B] [B]&[/B] [COLOR=brown]"*"[/COLOR] [COLOR=blue]Then[/COLOR]
          dar.Add [COLOR=blue]CStr[/COLOR][B]([/B]vList[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]))[/B]
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]Next[/COLOR]
       [I][COLOR=seagreen]'dar.Sort 'if you need to sort the list [/COLOR][/I]
       ListBox1.List [B]=[/B] dar.toarray[B]()[/B]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[pimp_mentality]

Good sir,

thanks for the assistance. however, when I integrate the code nothing happens when i test by typing into the designated TextBox nothing happens.

i suspect the problem may be with the last line of code ListBox1.List = dar.toarray(). I make this assumption as the listbox is populated by the content from the workbook. but hey i could be spouting utter rubbish

 

[Akuini]

Where do you put the name list?
Try this workbook as an example:
Dropbox - a1016586 - Listbox search as you type.xlsm

 

[mikerickson]

If you are using RowSource to populate the ListBox, this code won't work. But, since every name matches a blank text box, your current "fill the list box" can be replaced with this.

(BTW, if you are going to be using this cross-platform, be aware that Mac does not support the dictionary object)

 

[pimp_mentality]

ok i think thats the problem, RowSource is being used to populate the list box

 

[pimp_mentality]

any idea on how to modify the code to account for RowSource

 

[Akuini]

any idea on how to modify the code to account for RowSource

Sorry, I don’t know how to do that. Maybe somebody else could help.
But, is it really necessary to use RowSource?
It’s simpler to use code like this (note: the RowSource must be empty):

[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] UserForm_Initialize[B]()[/B]
ListBox1.List [B]=[/B] Sheets[B]([/B][COLOR=brown]"sheet1"[/COLOR][B]).[/B]Range[B]([/B][COLOR=brown]"A2"[/COLOR][B],[/B] Sheets[B]([/B][COLOR=brown]"sheet1"[/COLOR][B]).[/B]Cells[B]([/B]Rows.Count[B],[/B] [COLOR=brown]"A"[/COLOR][B]).[/B][COLOR=blue]End[/COLOR][B]([/B]xlUp[B])).[/B]Value
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[pimp_mentality]

you are right sir, did it your way and works great now, Much thanks.

Now I have a new problem though

I had a line of code which would display the number or entry in the Database within a small textbox above the list box her is the code

Private Sub UserForm_Initialize()

ListBox1.List = Sheets("PI").Range("B2", Sheets("PI").Cells(Rows.Count, "B").End(xlUp)).Value

    
[B]    TextBox9.Text = LastRow - 1[/B]


Application.Run "Arrange"


End Sub

Now the code does not work, in the designated TextBox all I see now is -1 any suggestions?

 

[Akuini]

What is LastRow?
If you mean the last item in the listbox you can do this:

TextBox9.Text = ListBox1.List(ListBox1.ListCount - 1)