MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

filtering drop down list selection

  Asked By: Adelina    Date: Feb 09    Category: MS Office    Views: 2142

I need clues on how I can start going about doing this. I have a drop
down list field connected to a data list full of names of people.
Whenever the user inputs an additional character, the drop down list
only shows the subset starting with the additional characters.

For example, if the user starts typing 'J', the drop down list would
only show names starting with 'J' from the datalist. If the user
types another character like 'Jo', the drop down list would list now
names starting with 'Jo' and so forth.

Pointing me to the right direction would be greatly appreciated. Many
thanks in advance.



9 Answers Found

Answer #1    Answered By: Bes Massri     Answered On: Feb 09

Your post came in from 2 different sources. So which is the real Bud? :-)

Anyway... If I understand you correctly, you will have to repopulate the
"dropdown" everytime the user  types something in. How many names  are we
talking about? What's the data  source? If you're picking them up with an SQL
statement it may be easier to filter. OTOH, If the data list  is sorted and
there aren't too many names then it may not be nessesary to do anything
because typing the first letter should jump the list to the first name.

Answer #2    Answered By: Naba Malik     Answered On: Feb 09

The list  of addresses will be coming from a datalist  on another
worksheet (but within the same workbook). No fancy SQL database and
just a simple datalist. The datalist has fields or columns
companyname (unique master index field), address, telephone, and
contact person. The list will be presorted and should carry no more
than 300 records.

You are correct that I will have to repopulate the dropdown  list
everytime a key is pressed in that certain input field  (or worksheet
cell). Any ideas on the commands to repopulate and only output the
sorted records?

Answer #3    Answered By: Elizabeth Hughes     Answered On: Feb 09

I'm playing with this at the moment and I'd really like to have
a look at the code oschua has if it can be posted.

What i have so far has a peculiarity and I've posted it under another
subject hoping someone could help.

You'll find it with the title "2003 - Userform combobox behaviour".

I think it would be just as good to just sort and populate though. As you
type each letter the selection  jumps down the list  in any case.... And you
still have to actually drop  down the drop down so to speak.

I've done something like this in Access but I use a text box and a listbox.
It's a little bit easier to manage and I was using SQL of course. Also,
using a listbox the selection is always visible so no need to activate a
drop down.

My guess is though that you want a drop down because of space on the form.

If I get any further I'll post an answer but I think your best bet at the
moment is to hope oschua comes up with code.

Answer #4    Answered By: Arlene Harvey     Answered On: Feb 09

Nope Im pretty much indifferent whether its a dropdown  or listbox.
Either way would be fine.

Also, having the first proper record highlighted in the listbox is
also acceptable rather than having the listbox choices repopulated.

Answer #5    Answered By: Caroline Bowman     Answered On: Feb 09

Here's what I have so far...

Note I use *double click* to put stuff into the listbox. This is because the
click event is triggered when you select an item so we get in an endless
loop thingy. I'm trying to think of a way around this... Maybe use different
events for the controls... But I think otherwise it fits your bill.

I'm testing with a plain userform with one textbox txt1 and one listbox

Answer #6    Answered By: Karla Ortiz     Answered On: Feb 09

but its going to take a few days or awhile for
me to digest the code you wrote down there ...

In the meantime, Im trying to break this problem into smaller
manageable portions and solve them one at a time baby steps.

My first stumbling block is the idea of how to repopulating a listbox.
Without doing any VBE coding yet and just trying to figure out how to
do this 'manually'. I create a custom filter to filter the records in
the datalist  to just show  the simulated starting  characters I want to
display. The datalist shows  the updated filtered list.

I expected the listbox (whose input is connected  to the datalist) to
automatically repopulate with the new updated list  but I was clearly
mistaken. The listbox did not repopulate with the new updated
filtered list and just kept the original list. What is the command or
steps to have a listbox repopulate?

Answer #7    Answered By: Anu K     Answered On: Feb 09

i did this a while ago and i don't have the code ready... but to help you get
started i had to first sort the list... and then i got from the internet an
auto complete code using the windows api... if no one else has a readily
available answer i'll look through my old stuff and see if i can find it again

Answer #8    Answered By: Fjodor Bonkob     Answered On: Feb 09

Would appreciate to have a copy of the auto complete code. Many thanks.

Answer #9    Answered By: Faizah Khan     Answered On: Feb 09

I have a working solution for this question!

Didn't find what you were looking for? Find more on filtering drop down list selection Or get search suggestion and latest updates.