Tom’s Tutorials For Excel: Copying Multiple ListBox Selections to Worksheet

Tom’s Tutorials For Excel: Copying Multiple ListBox Selections to Worksheet

Here’s how to transfer multiple selected items from an ActiveX ListBox onto your spreadsheet without intervening empty cells.



Private Sub cmdConfirm_Click()
'Turn off ScreenUpdating.
Application.ScreenUpdating = False

'Declare variables for row and ListBox item index.
Dim xRow As Integer, intItem As Integer

'The list will be transferred to column A.
'Clear column A to start with a clean slate.
Columns(1).Clear

'In cell A1, enter the column header and bold the cell.
With Range("A1")
.Value = "Shopping List Selections"
.Font.Bold = True
End With

'Because cell A1 is a header cell, have row 2 be the first
'row where ListBox items with start being transferred.
xRow = 2

'Open a With structure for the lbxShoppingList ListBox.
With lbxShoppingList
'Loop through each ListBox item to see if it is selected.
'If it is selected, transfer it to the next available row,
'and add a 1 to the xRow (next available row) variable.
For intItem = 0 To .ListCount - 1
If .Selected(intItem) = True Then
Cells(xRow, 1).Value = .List(intItem)
xRow = xRow + 1
End If
Next intItem
'Close the With structure.
End With

'Autofit column A for readability and neatness!
Columns(1).AutoFit

'Turn ScreenUpdating back on.
Application.ScreenUpdating = True
End Sub