listboxs and combo boxes

[Rich Cooper]

I am working with both a combo box and a list box. The list box has a bunch
of names. The combo box has some options and i want those options to sort
and filter the listbox. How do i do that? Some of the options are by
state, zipcode, city, last nmae, firstname? Any help wouldbe greatly
appreciated.

 

[Tom Ogilvy]

You say the listbox has a bunch of names - then you talk about things like
zipcode, state, as so forth. Is this a multicolumn listbox with all that
information for each name?

There is no built in method to sort a listbox's list.

Other than that, you would have to grab the list as an array, sort it
according to your selection in the combobox, then put it back in the
listbox.

What kind of help are you looking for?

 

[Rich Cooper]

It is a single column list book. The data is on another worksheet. I have
the list box using a range of nmaes and using the index i am able to get
the other info about the person and display it in a txtbox. I just want to
know if i can sort the data the same way. Or do i have to make it an array.
If i make it an array how do i go about doing that.

 

[Tom Ogilvy]

just use the sort command in Excel. Use the options in the combobox to
specify the sort key. If you mean reorder the data in the listbox as well,
then just reload the listbox from the data on the worksheet or if you are
using the rowsource, I would think it would update automatically.

 

[Dick Kusleika]

Rich

Here are two options for you to consider. First, put all the information
from the range in the listbox (you can hide columns you don't want the user
to see by making it's width 0) and populate the textboxes using the Column
property of the listbox.

Another option is not to use the index to find the other data. You could
use the Find method, for instance, to find the row that matches the listbox
and populate the textboxes based on that.

Either way, you need to put your data (either 1 column or all columns) into
an array and sort the array before you populate the listbox.

The bonus third option is to sort the range that contains the data before
you populate the listbox. I don't know if you can do that, however.

To sort a listbox, see here
http://www.dicks-blog.com/excel/2004/05/sorting_listbox.html

To sort a multicolumn listbox, see here
http://www.dicks-blog.com/excel/2004/05/sorting_a_multi.html

A Find Method example can be seen here
http://www.dicks-blog.com/excel/2004/03/the_find_method.html

 

[Tom Ogilvy]

Dick's links would take a lot of time to adapt to sort an array of more than
two columns. If you are up to the task, drive on, but it would be easier to
sort on the sheet using the built in sort, then grab the whole range and put
it in the list in one step.

 

[Dick Kusleika]

That's a good point, Tom. If, for some reason, you couldn't sort the data
on the worksheet, it would probably still be easier to create a new
worksheet, copy the data, sort it, populate the listbox (with AddItem) and
delete the sheet.

 

[Tom Ogilvy]

populate the listbox (with AddItem)

or

Listbox1.List = TempSheet.Range("A1").CurrentRegion.Value