EXCEL VBA - 带有多选用户窗体列表框的数据输入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25770641/
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
EXCEL VBA - Data Entry with Multi-Selection UserForm List Box
提问by confused
I'm trying to create a User Form that allows someone to pick some options and enter data into my excel sheet. In the user form, I have a List Box with several answers. I have it so that that the User can select multiple answers in the List Box.
我正在尝试创建一个用户表单，允许某人选择一些选项并将数据输入到我的 Excel 表中。在用户表单中，我有一个包含多个答案的列表框。我有它以便用户可以在列表框中选择多个答案。
If the User selects 2 answers, I want the excel sheet to register 2 rows of data. If the user selects 3 answers, I want the excel sheet to register 3 rows of data.
如果用户选择 2 个答案，我希望 Excel 表注册 2 行数据。如果用户选择 3 个答案，我希望 excel 表注册 3 行数据。
Basically I am doing exactly what is described here: http://www.excel-easy.com/vba/userform.htmlexcept in the "City Preference" ListBox, I can select multiple choices. I want the excel sheet to create a line item for each City Preference chosen, while holding all the other selections the same.
基本上我正在做这里描述的事情：http: //www.excel-easy.com/vba/userform.html除了在“城市首选项”列表框中，我可以选择多个选项。我希望 Excel 表为选择的每个城市首选项创建一个行项目，同时保持所有其他选择相同。
I'm thinking the code will be something like this:
我认为代码将是这样的：
For i = 1 to "total # of items selected in listbox"
emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1
Worksheet.Cell(emptyrow,3).Value = "Selected item(i) from list box"
Next I
Thanks!
谢谢！
回答by David Zemens
Use a function like this to return an array of selected items:
使用这样的函数返回选定项目的数组：
Public Function GetSelectedItems(lBox As MSForms.ListBox) As Variant
'returns an array of selected items in a ListBox
Dim tmpArray() As Variant
Dim i As Integer
Dim selCount As Integer
selCount = -1
For i = 0 To lBox.ListCount - 1
If lBox.selected(i) = True Then
selCount = selCount + 1
ReDim Preserve tmpArray(selCount)
tmpArray(selCount) = lBox.List(i)
End If
Next
If selCount = -1 Then
GetSelectedItems = Array()
Else:
GetSelectedItems = tmpArray
End If
End Sub
Then modify your code like:
然后修改你的代码，如：
Dim selectedItems as Variant
selectedItems = GetSelectedItems(myListBox) 'Modify this line to refer to your listbox name
For i = lBound(selectedItems) to UBound(selectedItems)
emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1
Worksheet.Cell(emptyrow,3).Value = selectedItems(i)
Next
