Userform connected dropdowns (WinNT/XL97)

[BerylM]

I have a filter system to use with a mailmerge 'database' which works perfectly as long as we want to filter by one of the set combinations given on the userform. By the way, the system removes all the items found by this filtering to a new spreadsheet, which is why we don't just use autofilter!

However, I would like to add a custom filter to this, with two dropdown menus - the first will populate with the column headings, then on the user selecting the heading they want, the second would auto-populate with the possible options in that column.

I presume I also want to add an option to say "there are more than # options in this column, please choose another column to filter on", so they don't try to filter by address or something ridiculous (these 'databases' sometimes contain 10s of 1000s of entries!), where # is the maximum number of items you can populate a dropdown box with - I seem to remember seeing somewhere that this was 25, but I'm not sure.

I'm pretty certain I can do the code behind it that actually does the work ( <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> !) but would someone point in the right direction as to how to get started on setting up the two dropdowns, please?

I would be most grateful!

[pieterse]

As you already said, you will need to include code that populates both dropdowns, using a for-each loop to loop the rows and the AddItem method to add their headings for the first dropdown. in the Change event of that first dropdown you'll have to add code that populates the second (take care here, because the adding of items to the first will also fire it's change event, so you'll need to take some precautions there).

A userform dropdown can contain at least 20,000 items (I use two of them with my Flexfind searching utility and it has once found an odd 25,000 hits, populating the two multicolumn listboxes without trouble).

[BerylM]

Thanks, Jan, but although I understand generally what you're saying, I haven't got the faintest idea how to go about implementing it!

Any chance of some sample code? I usually find that although I'll know what it is I've got to do I'll end up spending hours trying to find the right keywords and syntax to get it to work!

<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

[HansV]

You probably won't want to hear this, but I think that a "real" database (Access, for example) would be much more suitable for what you're doing.

Anyway, I have attached a VERY simplistic example. It doesn't attempt to sort the items or to list unique items only, it only displays the values from the selected column. And the form doesn't do anythimg useful - it only demonstrates how to populate combo boxes in a userform.

[sdckapr]

John Walkenbach has some code for Filling a ListBox With Unique Items which is almost identical to filling a combobox.

Steve

[BerylM]

Thanks, and I knew you'd say that (should be in Access) but there are reasons why this is not an option!

<img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> ... apart from anything else, I can't program in Access ... but that is, honestly, a very minor reason in a long list of major ones.

I haven't looked at the attachment yet, but I know one thing I'm going to ask - how do I get the list of unique options with which to populate the second dropdown! That's one of the things I've drawn a complete blank on (hopefully the others will be covered in your example).

Many thanks again!

[BerylM]

Aha - you must have read my mind! (see my last post to Hans!).

Thanks for that - I'll go check it out soon.

[HansV]

The attached modified version uses the code suggested by Steve to populate the second combo box with sorted, unique items.

Note: John Walkenbach's code uses bubble sort to sort the list. This is the simplest, but also the least efficient sorting algorithm. If you're working with tens of thousands of records, it may be too slow. We can suggest faster methods if you need them.

[sdckapr]

Another option which could speed it up and use even less coding, is to let excel do the "heavy lifting".

You can create a temp worksheet, and create a pivot table from the raw data. The pivot table routine can create a unique sorted list and then you just have to use this range to populate the list (with additme or even the "Fill range")

Steve

[BerylM]

Now that sounds like a sensible idea - I'm tied up with something else at the moment and haven't had a chance to actually look at all the suggestions made so far, but I do intend to look at them all as soon I can get back to it! Might be a day or two, though, so don't think I'm ignoring you if it takes a while!

Many thanks!