vba 从工作表上的列表框中获取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9821968/
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
Get values from a listbox on a sheet
提问by Bruder
I have a listbox named ListBox1 on Sheet1 of an Excel workbook.
我在 Excel 工作簿的 Sheet1 上有一个名为 ListBox1 的列表框。
Every time the user selects one of the items in the list, I need to copy its name to a variable named strLB.
每次用户选择列表中的一项时，我都需要将其名称复制到名为 strLB 的变量中。
So, if I have Value1, Value2, Value3, Value4 and the user selects Value1 and Value3, I need my strLB to come out as Value1,Value3.
因此，如果我有 Value1、Value2、Value3、Value4 并且用户选择 Value1 和 Value3，我需要我的 strLB 以 Value1、Value3 的形式出现。
I tried doing that post hocwith:
我尝试使用以下方法进行事后处理：
For i = 1 To ActiveSheet.ListBoxes("ListBox1").ListCount
If ActiveSheet.ListBoxes("ListBox1").Selected(i) Then strLB = strLB & etc.etc.
Next i
But this is very slow (I have 15k values in my listbox). This is why I need to record the selection in real time and not in a cycle, after the user is done inputting.
但这非常慢（我的列表框中有 15k 个值）。这就是为什么我需要在用户完成输入后实时记录选择而不是循环记录。
I'm going to also need a way to check if the user removed any of the previous selection.
我还需要一种方法来检查用户是否删除了之前的任何选择。
采纳答案by Siddharth Rout
Unfortunately for MSForms list box looping through the list items and checking their Selected property is the only way. However, here is an alternative. I am storing/removing the selected item in a variable, you can do this in some remote cell and keep track of it :)
不幸的是，MSForms 列表框循环遍历列表项并检查它们的 Selected 属性是唯一的方法。但是，这里有一个替代方案。我在变量中存储/删除所选项目，您可以在某个远程单元格中执行此操作并跟踪它:)
Dim StrSelection As String
Private Sub ListBox1_Change()
If ListBox1.Selected(ListBox1.ListIndex) Then
If StrSelection = "" Then
StrSelection = ListBox1.List(ListBox1.ListIndex)
Else
StrSelection = StrSelection & "," & ListBox1.List(ListBox1.ListIndex)
End If
Else
StrSelection = Replace(StrSelection, "," & ListBox1.List(ListBox1.ListIndex), "")
End If
End Sub
回答by Joan-Diego Rodriguez
The accepted answer doesn't cut it because if a user de-selects a row the list is not updated accordingly.
接受的答案不会削减它，因为如果用户取消选择一行，则列表不会相应地更新。
Here is what I suggest instead:
这是我的建议：
Private Sub CommandButton2_Click()
Dim lItem As Long
For lItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem) = True Then
MsgBox(ListBox1.List(lItem))
End If
Next
End Sub
Courtesy of http://www.ozgrid.com/VBA/multi-select-listbox.htm
回答by matt2405
To get the value of the selected item of a listbox then use the following.
要获取列表框所选项目的值，请使用以下内容。
For Single Column ListBox:
ListBox1.List(ListBox1.ListIndex)
对于单列列表框：
ListBox1.List(ListBox1.ListIndex)
For Multi Column ListBox:
ListBox1.Column(column_number, ListBox1.ListIndex)
对于多列列表框：
ListBox1.Column(column_number, ListBox1.ListIndex)
This avoids looping and is extremely more efficient.
这避免了循环并且非常高效。
回答by Alex
Take selected value:
取选定的值：
worksheet name = ordls
form control list box name = DEPDB1
selectvalue = ordls.Shapes("DEPDB1").ControlFormat.List(ordls.Shapes("DEPDB1").ControlFormat.Value)
