vba 如何从2张Excel中提取数据并在VB表单字段中显示
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6000230/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Extract data from 2 sheets of excel and display them in VB form fields
提问by mark
I have data in 2 sheets excel say sheet2 and sheet3 and values in column1 of both sheets is same. So i created a userform with 1 dropdown(Combobox1), 4 textbox field and 1 listbox field.
我有 2 张 Excel 中的数据，比如 sheet2 和 sheet3，并且两张工作表的 column1 中的值是相同的。所以我创建了一个包含 1 个下拉列表（Combobox1）、4 个文本框字段和 1 个列表框字段的用户表单。
dropdown picks data from column 1(ex. A2:20) of sheet2 and then once a value is selected it should display corresponding row values in textbox respectively for example: from sheet2 corresponding col-2 should be displayed in textbox1, col-3 in textbox2, col4 in textbox3, col5 in textbox4. while from sheet3 it should match the row where column1 value matched the selected value from from dropdown(combobox) and all corresponding values(in Col 2 - Col52) should be displayed in userform ListBox1. I am new to vba and excel so with help of google finally i was able to perform some of above mentioned tasks but i am not getting any head and tail how to connect selected range values to textbox'es and listbox. Any help will be highly appreciated and will be a good learning lesson for me. here's the half and not working code i created.
下拉列表从sheet2的第1列（例如A2：20）中选择数据，然后一旦选择了一个值，它应该分别在文本框中显示相应的行值，例如：从sheet2中对应的col-2应显示在textbox1中，col-3中文本框2，文本框3中的col4，文本框4中的col5。而从 sheet3 中，它应该匹配 column1 值与从下拉列表（组合框）中选择的值相匹配的行，并且所有相应的值（在第 2 列 - 第 52 列中）应该显示在用户表单 ListBox1 中。我是 vba 和 excel 的新手，所以在谷歌的帮助下，我终于能够执行上面提到的一些任务，但我没有得到任何关于如何将选定的范围值连接到文本框和列表框的头尾。任何帮助都将受到高度赞赏，对我来说将是一个很好的学习课程。这是我创建的一半而不是工作代码。
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Dim CL As Object
Worksheets(2).Select
For Each CL In Worksheets(2).Range("A2:A20")
If CL = ComboBox1.Text Then
Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy Destination:=ActiveCell.Offset(0, 5)
End If
Next
Worksheets(2).Select
End Sub
Private Sub UserForm_Activate()
ComboBox1.RowSource = "A2:A20"
End Sub
采纳答案by Alex P
This may get you on the right track:
这可能会让你走上正轨：
Private Sub ComboBox1_Change()
Dim rw As Long, cl As Range
rw = WorksheetFunction.Match(Me.ComboBox1.Value, Worksheets("Sheet2").Range("A1:A20"), 0)
TextBox1 = Worksheets("Sheet2").Range("A" & rw).Offset(0, 1)
TextBox2 = Worksheets("Sheet2").Range("A" & rw).Offset(0, 2)
TextBox3 = Worksheets("Sheet2").Range("A" & rw).Offset(0, 3)
TextBox4 = Worksheets("Sheet2").Range("A" & rw).Offset(0, 4)
ListBox1.Clear
For Each cl In Worksheets("Sheet3").Range("B" & rw & ":AZ" & rw) //Here AZ is column 52 in worksheet
ListBox1.AddItem cl
Next cl
End Sub
Private Sub UserForm_Activate()
ComboBox1.RowSource = "A1:A20"
End Sub
Update
更新
If you want the ListBox to show data horizontally then:
如果您希望 ListBox 水平显示数据，则：
- In ListBox properties set
ColumnHeadstoTrue - In ListBox properties set
ColumnCountequel to 52
- 在 ListBox 属性中设置
ColumnHeads为True - 在 ListBox 属性中将
ColumnCountequel设置为 52
Now replace For each cl...code aboe with:
现在将For each cl...代码 aboe替换为：
Dim rng As Range
Set rng = Worksheets("Sheet3").Range("B" & rw & ":AZ" & rw)
Me.ListBox1.RowSource = rng.Address
