Search code examples
excelvbacomboboxuserform

Extract data from column of selected row in excel vba userform combobox

I have a combobox (cbInventory) populated with a named range in the RowSource property that is a table that contains 6 fields and many thousands of rows. The bound column is the first column (Material). When an item is selected in the combobox, I want the batch number associated with the selected item to automatically appear in a separate text box (using a Change event for cbInventory).

enter image description here

So for example, if A100-114P is selected, the number 11 should appear in the text box.

Private Sub cbInventory_Change()
    Me.txtBatch = Me.cbInventory.Column(1, ?)
End Sub

If I manually enter 1 in place of the question mark above, the number 10 appears in the text box, which makes sense. Now, what do I put in place of the question mark to pull the batch number of the selected item?

Solution

  • column(0) would be the 1st column.

    Private Sub CommandButton1_Click()
    MsgBox Me.ComboBox1.Column(0)
    MsgBox Me.ComboBox1.Column(1)
    MsgBox Me.ComboBox1.Column(2)

End Sub