用vba Excel中的文本框链接Combobox

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

我在Excel中创建了一个Userform来方便地将文本输入到工作表中。

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

我有第二个工作表，其中包含必须填充到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.

这个表格包含两列，一列(A)有数字，第二列(B)有与这些数字相关的项目的描述。

Currently I have the first part of the population working.

目前我有第一部分的人口工作。

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

使用此代码填充项目编号的combobox:

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?

我的问题是，我应该在表单中写什么代码，以便当我通过combobox选择一个条目(数字)时，必须包含描述的文本框会自动填充?

1 个解决方案

#1

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.

在组合框循环的更改事件中，通过列a值。当您找到匹配时，将列b值放在文本框中。

    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