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.
'In cell A1, enter the column header and bold the cell.
.Value = "Shopping List Selections"
.Font.Bold = True
'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.
'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
'Close the With structure.
'Autofit column A for readability and neatness!
'Turn ScreenUpdating back on.
Application.ScreenUpdating = True
I would like to know how you figure out this kind of codes. excellent Job
Thank you! I enjoy spending time seeing and testing what Excel can do, and then sharing the knowledge.