Link Combobox with Textbox in vba Excel

Question

I'm making a Userform in Excel to easily input text into a worksheet.

I have a second worksheet containing information that has to be populated into the userform.

This sheet contains 2 columns, one column (A) has numbers and the second one (B) has the description of the items linked to these numbers.

Currently I have the first part of the population working.

A combobox is being populated with the item numbers using this code:

Private Sub UserForm_Initialize()
    With Worksheets("nummers")
        cobProductNr.List = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    End With
End Sub

My question is, what code do I write in my form so that when I select an item(number) through my combobox, a textbox that has to contain the description automatically gets filled in?

MatthewD MatthewD · Accepted Answer · 2015-07-08T13:44:06

In the change event of the combo box loop through the column a values. When you find the match, put the column b value in the text box.

    Private Sub ComboBox1_Change()
    Dim lRow As Long

    'Now go through and check the values of the first column against what was selected in the combo box.
    lRow = 1
    Do While lRow <= ws.UsedRange.Rows.Count

        If ws.Range("A" & lRow).Value = ComboBox1.Text Then
            Text1.Text = ws.Range("B" & lRow).Value
            Exit Do
        End If

    lRow = lRow + 1
    Loop

    End Sub

Link Combobox with Textbox in vba Excel

1 Answers