使用这样的函数返回所选项的数组:
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
然后修改你的代码,如:
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