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
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
#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