在列表框 vba 中查找所选项目的行#
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23050647/
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
find a row # of the selected item in listbox vba
提问by Doolie1106
How can i find the row # of selected item in listbox?
如何在列表框中找到所选项目的行号？
right now i have a subthat inputs all of the found item into the listbox, see below
现在我有一个sub将所有找到的项目输入到列表框中，见下文
Sub findnext()
Dim Name As String
Dim f As Range
Dim ws As Worksheet
Dim s As Integer
Dim findnext As Range
Set ws = ThisWorkbook.Worksheets("Master")
Name = surname.Value
ListBox1.Clear
Set f = Range("A:A").Find(what:=Name, LookIn:=xlValues)
Set findnext = f
Do
Debug.Print findnext.Address
Set findnext = Range("A:A").findnext(findnext)
ListBox1.AddItem findnext.Value
ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Cells(findnext.Row, xFirstName).Value
ListBox1.List(ListBox1.ListCount - 1, 2) = ws.Cells(findnext.Row, xTitle).Value
ListBox1.List(ListBox1.ListCount - 1, 3) = ws.Cells(findnext.Row, xProgramAreas).Value
ListBox1.List(ListBox1.ListCount - 1, 4) = ws.Cells(findnext.Row, xEmail).Value
ListBox1.List(ListBox1.ListCount - 1, 5) = ws.Cells(findnext.Row, xStakeholder).Value
ListBox1.List(ListBox1.ListCount - 1, 6) = ws.Cells(findnext.Row, xofficephone).Value
ListBox1.List(ListBox1.ListCount - 1, 7) = ws.Cells(findnext.Row, xcellphone).Value
'ListBox1.List(ListBox1.ListCount - 1, 8) = ws.Cells(findnext.Row, xFirstName).Value
Loop While findnext.Address <> f.Address
End Sub
and if the user selects the item in the listbox, then it'll populate the information into the textbox of the userform
如果用户选择列表框中的项目，那么它会将信息填充到用户表单的文本框中
Sub ListBox1_Click()
surname.Value = ListBox1.List(ListBox1.ListIndex, 0)
firstname.Value = ListBox1.List(ListBox1.ListIndex, 1)
tod.Value = ListBox1.List(ListBox1.ListIndex, 2)
program.Value = ListBox1.List(ListBox1.ListIndex, 3)
email.Value = ListBox1.List(ListBox1.ListIndex, 4)
SetCheckBoxes ListBox1.List(ListBox1.ListIndex, 5) '<<<< added
officenumber.Value = ListBox1.List(ListBox1.ListIndex, 6)
cellnumber.Value = ListBox1.List(ListBox1.ListIndex, 7)
End Sub
i want to figure out from ListBox1_click()how i can determine the row # of the selected item in the listbox. once i figure this out, i'll code an update subwhere it'll locate the row # of the selected item and i'll re-enter the information in the textboxes and update the information of the row.
我想弄清楚ListBox1_click()如何确定列表框中所选项目的行号。一旦我弄清楚了这一点，我将编写一个代码update sub，它将定位所选项目的行号，然后在文本框中重新输入信息并更新该行的信息。
i thought about storing a row # in a hidden worksheet when i do find.. but i don't know how to link the row # of the foundwith what's been selected in the listbox
当我这样做时，我想在隐藏的工作表中存储一行 #find但我不知道如何将行 #found与在listbox
hopefully...this makes sense, if not please let me know!
希望......这是有道理的，如果没有，请告诉我！
回答by Doolie1106
I've found a way to go around this - i've made an extra listcolumn to store the row#of the foundwhen the item's been searched. then i made another textbox within the userform that would input the row#:
我已经找到一种方法去解决这个-我做了一个额外的listcolumn来存储row#的found，当项目的被搜索。然后我在用户表单中创建了另一个文本框来输入行#：
therefore, within the findnext()sub i added the following line storerownumber = findnext.rowand added another line for listbox. `listbox1.list(listbox1.listcount-1,8) = storerownumber
因此，在findnext()sub 中，我添加了以下行storerownumber = findnext.row并为列表框添加了另一行。`listbox1.list(listbox1.listcount-1,8) = storerownumber
and within the listbox1_click()sub i've added the following line: rownumber.value = listbox1.list(listbox1.listindex,8)
在listbox1_click()sub 中，我添加了以下行：rownumber.value = listbox1.list(listbox1.listindex,8)
and made a new sub update_click()and added the following lines:
并做了一个新的sub update_click()并添加了以下几行：
Dim r As Long
Dim update As Range
Dim ws As Worksheet
Set ws = Worksheets("Master")
rownumber.Value = ListBox1.List(ListBox1.ListIndex, 8)
r = rownumber
ws.Cells(r, xLastName).Value = surname.Value
ws.Cells(r, xFirstName).Value = firstname.Value
ws.Cells(r, xTitle).Value = tod.Value
ws.Cells(r, xProgramAreas).Value = program.Value
ws.Cells(r, xEmail).Value = email.Value
ws.Cells(r, xStakeholder).Value = GetCheckBoxes
ws.Cells(r, xofficephone).Value = officenumber.Value
ws.Cells(r, xcellphone).Value = cellnumber.Value
end sub
and it works fine!
它工作正常！
回答by PatricK
As far as I know, you have to loop through all rows in ListBoxsince you can have Multi-Select.
据我所知，您必须遍历ListBox 中的所有行，因为您可以进行多选。
For r = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(r) Then
Debug.Print "You selected row #" & r + 1
End If
Next
