Listbox search question

[Robert Crandal]

I would like to create a Userform that contains an input box
(ie textbox) and a listbox or combobox. The listbox will
contain over 500 strings of employee names. The user
should be able to easily scroll through the listbox and find
any name they are looking for.

However, to make things even easier for the user, I thought
I would let the user type in a name inside the textbox. So,
if a user begins by typing "Pet", I want the listbox to be
reduced so it only shows names such as "Peterson",
"Petrowski", "Petti-Jones", etc.... The contents of the listbox
should grow or shrink depending on what is typed in the
textbox.

What is a good way to implement this? I know how to add
items to a listbox, but I'm not sure how to effectively make
the contents of the listbox depend on the contents of the
search string in the textbox.

Thanks for your help.

 

[Claus Busch]

Hi Robert,

Am Thu, 30 Aug 2012 03:53:11 -0700 schrieb Robert Crandal:

However, to make things even easier for the user, I thought
I would let the user type in a name inside the textbox. So,
if a user begins by typing "Pet", I want the listbox to be
reduced so it only shows names such as "Peterson",
"Petrowski", "Petti-Jones", etc.... The contents of the listbox
should grow or shrink depending on what is typed in the
textbox.

in properties for listbox or combobox choose for "MatchEntry" the option
"fmMatchEntryComplete"


Regards
Claus Busch

 

[Robert Crandal]

Hmmmm, that's not quite was I was looking for....

For example, if someone starts typing "Ro", I want the listbox to
remove all other items and show items that begin with "Ro",
such as "Roberts", "Robinson", "Rodriguez", etc....
The listbox should dynamically expand or reduce its items
depending on the search string typed in the textbox.

 

[RB Smissaert]

Well, you need to act on the _Change event of that textbox.
So, every change of that textbox will trigger a Sub that may alter that
listbox.
This Sub will have an argument that is the text in the textbox, eg something
along
these lines:

Textbox1_Change()

AlterListbox Textbox1.text

End Sub

Sub AlterListbox(strText As String)

Dim i As Long

Listbox1.ListItems.Clear 'with a small list probably better than adding
and removing

If Len(strText) = 0 Then Exit Sub

'arrNames is a 1-D array holding your names, can be string array or
variant array
For i = 1 To UBound(arrNames)
If Left$(arrNames(i), Len(strText)) = strText Then
'add the element arrNames(i) to the listbox
End If
Next i

End Sub


RBS

 

[Howard]

I would like to create a Userform that contains an input box

(ie textbox) and a listbox or combobox. The listbox will

contain over 500 strings of employee names. The user

should be able to easily scroll through the listbox and find

any name they are looking for.



However, to make things even easier for the user, I thought

I would let the user type in a name inside the textbox. So,

if a user begins by typing "Pet", I want the listbox to be

reduced so it only shows names such as "Peterson",

"Petrowski", "Petti-Jones", etc.... The contents of the listbox

should grow or shrink depending on what is typed in the

textbox.



What is a good way to implement this? I know how to add

items to a listbox, but I'm not sure how to effectively make

the contents of the listbox depend on the contents of the

search string in the textbox.



Thanks for your help.

Hi Robert,

Here ia a NON-vb method that "kinda does that" using Data Valadation at theworksheet level. Perhaps worth a look.

At the bottom of your list of names, add A TO Z in the next 26 rows, in caps and bold font. Now select the entire list including the newly added A toZ and sort the list using the A to Z icon.

Select the cell you want the name to be displayed in and using Data Valadation > Settings > Allow: > List > check > Ignore blanks & In-cell DropDown boxes > click in the Source box > Select your entire list on the worksheet (leave the list high-lighted) > OK.

So now, in the data valadation cell, enter any single letter, say "R" and LEAVE IT AS THE ACTIVE CELL, do NOT hit ENTER.

Now click the Drop-Down arrow, this will take you to the beginning of the Rlist of names. Scroll to the name you want in the R list and click on it.

HTH
Regards,
Howard