Transfer Multi-Select ListBox To Range Of Cells
Category: [Excel] Demo Available
Transfer Multi-Select ListBox To Range Of Cells
Transfer Selected Rows Of Multi-Select & Multi-Column ListBox To Range Of Cells On Worksheet
The VBA code below will transfer all selected rows and columns of a multi-selected listbox. On loading the user form, the ColumnCount Property of the ListBox is set to as many columns as the RowSource Property.
Download working example
Private Sub UserForm_Initialize()
'Set ListBox ColumnCount to the same as RowSource
ListBox1.ColumnCount = Range(ListBox1.RowSource).Columns.Count
End Sub
Private Sub TransferButton_Click()
Dim lItem As Long, lRows As Long, lCols As Long
Dim bSelected As Boolean
Dim lColLoop As Long, lTransferRow As Long
'Pass row & column count to variables
'Less 1 as "Count" starts at zero
lRows = ListBox1.ListCount - 1
lCols = ListBox1.ColumnCount - 1
'Ensure they have at least 1 row selected
For lItem = 0 To lRows
'At least 1 row selected
If ListBox1.Selected(lItem) = True Then
'Boolean flag
bSelected = True
'Exit for loop
Exit For
End If
Next
'At least 1 row selected
If bSelected = True Then
With Sheet1.Range("D1", Sheet1.Cells(lRows + 1, 4 + lCols)) 'Transfer to range
.Cells.Clear 'Clear transfer range
For lItem = 0 To lRows
If ListBox1.Selected(lItem) = True Then 'Row selected
'Increment variable for row transfer range
lTransferRow = lTransferRow + 1
'Loop through columns of selected row
For lColLoop = 0 To lCols
'Transfer selected row to relevant row of transfer range
.Cells(lTransferRow, lColLoop + 1) = ListBox1.List(lItem, lColLoop)
'Uncheck selected row
ListBox1.Selected(lItem) = False
Next lColLoop
End If
Next
End With
Unload Me
Else ' NO listbox row chosen
MsgBox "Nothing chosen", vbCritical
End If
End Sub
Download working example
See also:
| Index to Excel VBA Code |
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.