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?

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 &LT;&GT; -1 then
Part = Range("FirstField").Range("A1").Offset(ComboBox1.ListIndex,1)
End If

I think that returns it correctly.

Juan Pablo

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

Thanks For your help. This worked perfect!