Combobox display 1 value, access another

[lcoreilly]

Hi All,
I have a combobox that gets filled with the following code:

'load combobox with list of grantee institutions
With ComboBox1
For i = 5 To LastRow
.AddItem Worksheets("data").Cells(i, 2)
Next i
End With

End Sub

The drop down menu contains a list of schools, but the data element I
really need access to is the ID number, which is in column A. Is
there a way to access this value? Maybe using the offset function? I
can't seem to figure it out.

Thanks in advance.

 

[B Lynn B]

ComboBox lists can have multiple columns and you can arrange them however you
see fit, using the BoundColumn, ColumnCount, and ColumnWidth properties. In
this particular case if you set Bound Column to 2, ColumnCount to 2, and
ColumnWidth to something like "20 pt;0 pt", the control will display the list
of schools, but its value will equal the code stored from column A of the
sheet.

Adjust the "20 pt; 0 pt" to however wide you really want the first column to
be. Know that you can set the second column to show both the school and its
associated code side by side in the ComboBox dropdown list if that would be
helpful to your users. Also worth noting, the first item in the list is
index number 0, thus the "i - 5" bit of my added line of code.

With ComboBox1
For i = 5 To LastRow
.AddItem Worksheets("data").Cells(i, 2)
.List(i - 5, 2) = Cells(i, 1)
Next i
End With

 

[B Lynn B]

Sorry, I just realized I should have included one more bit of info. To
return the alternate value, you'll need to call it specifically. i.e.:

myValue = ComboBox1.Column(2)

I was thinking the BoundColumn would take care of that, but in my test file,
something about that theory isn't working. So the line above is how to get
at it directly.

 

[Dave Peterson]

One more:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
With Me.ComboBox1
If .ListIndex < 0 Then
'nothing selected
Beep
Exit Sub
End If

MsgBox .List(.ListIndex, 1)
End With
End Sub
Private Sub UserForm_Initialize()
Dim LastRow As Long
Dim iCtr As Long
Dim wks As Worksheet

Set wks = Worksheets("Data")

With wks
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "55;0" 'hide the second column
For iCtr = 5 To LastRow
.AddItem wks.Cells(iCtr, "B").Value
.List(.ListCount - 1, 1) = wks.Cells(iCtr, "A").Value
Next iCtr
End With
End Sub