vba 在vba中使用列表框定位和显示数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19003729/
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
Locate and display data using listbox in vba
提问by user2123999
I have tried this code to locate specific data from excel using List Box in VBA, It populated a list of names from sheet3 range(E7), then everytime I click an item/name on it the program should located the name from sheet3 and display the data on that row into their corresponding textboxes in my userform.But this doesn't work pecisely.Thanks.
我已经尝试使用此代码使用 VBA 中的列表框从 excel 中查找特定数据，它填充了来自 sheet3 范围（E7）的名称列表，然后每次我单击它的项目/名称时，程序都应该从 sheet3 中找到名称并显示将该行上的数据放入我的用户表单中相应的文本框中。但这并不奏效。谢谢。
Private Sub ListBox1_Click()
Dim isRow As Long
If Me.ListBox1.ListIndex > -1 Then
isRow = Me.ListBox1.ListIndex + 1
End If
Me.Label1 = Cells(sRow, 5)
Me.txt_Mon_in.Text = Cells(sRow,6)
End Sub
Populating data from Sheet3.
从 Sheet3 填充数据。
Private Sub Userform_Initialize()
Dim vCol As Variant
Dim Lrow As Long
Lrow = Sheets("Sheet3").UsedRange.Rows(Sheets("Sheet3").UsedRange.Rows.Count).Row
vCol = Sheets("Sheet3").Range("E7:E" & Lrow).Value
Me.ListBox1.List = vCol
End Sub
回答by
Im not quite sure what you are doing but try the below code
我不太确定你在做什么，但试试下面的代码
Private Sub Userform_Initialize()
Dim vCol As Variant
Dim Lrow As Long
Lrow = Sheets("Sheet3").UsedRange.Rows(Sheets("Sheet3").UsedRange.Rows.Count).Row
vCol = Sheets("Sheet3").Range("E7:E" & Lrow).Value
Me.ListBox1.List = vCol
End Sub
Private Sub ListBox1_Click()
Dim selectedName As String
Dim i As Long
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
selectedName = .List(i)
End If
Next i
End With
Dim c As Range
For Each c In Sheets(3).Range("E7:E" & Sheets(3).Range("E" & Rows.Count).End(xlUp).Row)
If c = selectedName Then
Label1 = Sheets("Sheet3").Cells(c.Row, 5)
txt_Mon_in.Text = Sheets("Sheet3").Cells(c.Row, 6)
End If
Next c
End Sub
the Listbox1_Click()sub will iterate over the column E in sheet 3 and put the name in the Label1control and will put offset of (0,1) of the found cell into the txt_Mon_incontol.
该Listbox1_Click()子将迭代在片3的柱E和在把名字Label1控制和所找到的小区进入的将把偏移（0,1）txt_Mon_in控逆变。
Sheet3
Sheet3
Userform
用户表单
Result
结果
