Listbox search question
 

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.

Listbox search question
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Listbox search question
 

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.


"Claus Busch" wrote in message
...
Hi Robert,

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


Regards
Claus Busch

Listbox search question
 

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


"Robert Crandal" wrote in message
...
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.


"Claus Busch" wrote in message
...
Hi Robert,

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


Regards
Claus Busch

Listbox search question
 

On Thursday, August 30, 2012 3:53:14 AM UTC-7, Robert Crandal wrote:
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 the worksheet 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 to Z 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 R list of names. Scroll to the name you want in the R list and click on it.

HTH
Regards,
Howard