Archive of Mr Excel Message Board

Back to Controls in Excel archive index
Back to archive home

Combo Box - Display list from one column, but use Data from another column

Posted by Mark on November 15, 2001 11:09 AM

I have a combobox - fairly simple - that pulls its list of options from a column defined:

With cmbfirstfield
.RowSource = "FirstField"
.ListIndex = 0
End With

It correctly displays a drop down list with that column. When somebody selects an item from the combobox, I'd like it to use the data from another field, for instance:

Somebody selects "Red Widget with blue thingy" from the list. I want the data returned to be the part number, that is in the column adjacent to the item in the list.

They select "Red Widget" and part number "B17284" gets returned.

Any suggestions?

Re: Combo Box - Display list from one column, but use Data from another column

Posted by Juan Pablo on November 15, 2001 11:22 AM

This isn't difficult. FirstField is a named range, that should be something like "$C$150:$C$164". So the trick is to use the offset starting from C150, down the number of the selected item in the combobox (From 0 to 14 in my example), and 1 column to the left, so this should return the value....

If ComboBox1.ListIndex <> -1 then
Part = Range("FirstField").Range("A1").Offset(ComboBox1.ListIndex,1)
End If

I think that returns it correctly.

Juan Pablo

Re: Combo Box - Display list from one column, but use Data from another column

Posted by Mark on November 15, 2001 11:53 AM

Thanks For your help. This worked perfect!

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.