PDA

View Full Version : multi column list box and combobox



crush
12-07-2006, 10:35 PM
Hi,

i'm wondering if anyone knows how to create a list box with two colums, where Column 1 will contain a list of values and Column two will have combobox options for populating column 2. i've attached an image of what i would llike to do...

thanks for any help
kindest regards
chris

Bob Phillips
12-08-2006, 03:27 AM
i'm wondering if anyone knows how to create a list box with two colums, where Column 1 will contain a list of values and Column two will have combobox options for populating column 2. i've attached an image of what i would llike to do...
I think that you will be unlucky with that. A control within a control, don't think so, not unless you build your own ActiveX control.

johnske
12-08-2006, 01:51 PM
Hi chris,

From the image you've attached, it looks like you may be after a data validation list - or maybe even auto-filter. Is column 2 to be populated with values from column 1 maybe?

crush
12-08-2006, 05:26 PM
Hello both, thanks for replying.

column 1 would contain a list of part names (variable depending on the project being analysed), column two would be used to contain the part familiy it belongs to. the part family (column 2) list would be a fixed list of options. since i don't know what the parts in column 1 will be, i need to provide a means for users to select the part family from a list of options.

i hope this is this making sense?

perhaps there is another way to do this. i've seen the type of contol i would like in other applications. but i don't know how to create such a control, i was hoping it was possible with the list box control...

any other ideas you have would be most appreciated

kindest regards
chris

johnske
12-08-2006, 05:59 PM
OK, yep, sounds like you're after a validation list...

You'll need a column (it can be anywhere in the workbook) for your part family list, select the entire column and go to Insert > Name > Define...

Now type in PartFamily (or any other name you like really) and click OK.

This creates a "named range" for your list that you'll use to populate your other column with - enter you parts list in this column.

Now, select the column where you want this list to be applied to, and click Data > Validation. Where it says 'Allow', use the dropdown list and select 'List'. Where it then says 'Source', type in =PartFamily and click OK.

If you click on any cell in this column, you'll now get a dropdown list, select a part family from that list and it's then entered into the cell.

You can also type other things in the cell that aren't on the list, but this can be tweaked to allow only the things on the list by going back to Validation, select Error Alert and make your choices in there.

Post back if this's not what you need or if you want any more help, or come back and use the thread tools up top to mark the thread Solved if it is... :)

crush
12-08-2006, 06:17 PM
hello, and thanks once again

you understand what i'm looking for very well. do you know if it is possible to do this same thing on a list box control on a user form? i doubt it is, but thats really what i'm looking for.

i may have to think up some other method to populate column two. one other idea i had was to use a check box in the list box, and have a seperate combo box with the 'part family' list. the user would check the column 1 items, then select an option from the combo box, and then click an 'apply' button. the value from the combo box would then be 'inserted' into column 2 where ever a check mark was...

if the first idea is not possible, does the second idea sound possible?

johnske
12-08-2006, 07:43 PM
hello, and thanks once again

you understand what i'm looking for very well. do you know if it is possible to do this same thing on a list box control on a user form? i doubt it is, but thats really what i'm looking for.

i may have to think up some other method to populate column two. one other idea i had was to use a check box in the list box, and have a seperate combo box with the 'part family' list. the user would check the column 1 items, then select an option from the combo box, and then click an 'apply' button. the value from the combo box would then be 'inserted' into column 2 where ever a check mark was...

if the first idea is not possible, does the second idea sound possible?No, not the same thing.

Not sure what you're want with the rest, but

i) Yes, you can put a check box in a list box

ii) There has been a couple of threads fairly recently (few weeks back maybe) about using one list box to get a shorter list for a second list box, it may pay you to search thru the older threads to find these rather than re-inventing the wheel...

Bob Phillips
12-09-2006, 03:46 AM
I think you want a Listbox and a combobox, and populate the combobox depending upon the listbox selection.

crush
12-09-2006, 10:05 AM
Hello, i'm attaching another image of what i would like to do in a user form within excel. column1 will be populated depending on the part being analysed. column 2 will require the user to select from available choices. i'm begining to think that this is not doable in an excel list box control on a user form? i think i need a custom control for this type of action? it may be time to close this question...

lucas
12-09-2006, 10:10 AM
Can you clarify please? Is the image of a multi-page userform with a worksheet added to the Properties tab?

crush
12-09-2006, 10:17 AM
no, this was an example from another application. is it possible to add a 'worksheet' to a user form? that maybe what i need...

Bob Phillips
12-09-2006, 10:26 AM
I re-iterate my suggestion

I think you want a Listbox and a combobox, and populate the combobox depending upon the listbox selection.

crush
12-09-2006, 10:41 AM
hello xld, the comob box options are a fixed list. the selection from the combo box would become the value entered into column 2 of the list box.

i'm currently looking at some other controls like the MSFlexGrid control...

lucas
12-09-2006, 11:26 AM
Bob or someone correct me if I'm wrong but I think crush is trying to use a different source for column 2 of a listbox than the source for column 1 which I think is not possible.....

Kicker
12-09-2006, 11:52 AM
I agree with xld

Bob Phillips
12-09-2006, 11:53 AM
I understand that Steve, but my reading of it is that the second list will be dependent upon what is picked in the first. It could be two dependent listboxes, or two dependent combos (I suggested a mix as that conformed more to the OP's pictures), and the second would need building depending upon what is selected, but that is trivial. As he hasn't said if or why that is not an option, I repeated it.

crush
12-09-2006, 12:51 PM
perhaps adding the spreadsheet will help. i want to get columns 1 and 2 in a user form.

when the list box loads, column1 will be populated with the part names. these names will change and also the quantity of parts will change.

column 2 for each part will initially be 'blank'. the idea is that for each part in column 1, the user will select from a list of options what value to enter in column 2. the list of options for column 2 entries will be a fixed list.

lucas
12-09-2006, 05:24 PM
I understand that Steve, but my reading of it is that the second list will be dependent upon what is picked in the first. It could be two dependent listboxes, or two dependent combos (I suggested a mix as that conformed more to the OP's pictures), and the second would need building depending upon what is selected, but that is trivial. As he hasn't said if or why that is not an option, I repeated it.
Hi Bob,
I only stated the obvious because crush does not seem to be understanding that this can't be done with one listbox and maybe be able to move on to one of your suggestions.....

johnske
12-09-2006, 06:49 PM
So you want a two column list box with column A containing a list, and column B blank - when an item in column A is selected, another list (or combo) box appears with a list of different items and when one of the items from the second box is selected this item appears in column B next to the selected item in column A?

Does that state the problem?

crush
12-09-2006, 07:21 PM
johnske, yes that does state the problem. i'm sorry if i'm causing confusion here, it was never my intention. i just assumed that in the applications where i see this type of control that it was a 'list box' but i guess it must be a special kind of control with such features.