Introduction:
This article gives two methods to fill a multi column listbox on a userform with data from an Excel worksheet.
- Pulling the worksheet range directly into a listbox
- Pulling the data into an array first, then placing the array in the listbox
The intention of both of these pieces of code is to be used in a userform. Both are set up in the Userform_Intialize event, although only one or other should be used.
Versions Tested:
These routines were tested successfully running Office 2003, but shouldn't have any issue in Excel 97 or higher.
Pulling the worksheet range directly into a listbox
Code:
Private Sub UserForm_Initialize()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: To populate a multi-column listbox with data from
' a worksheet range
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
'Set reference to the range of data to be filled
Set rngSource = Worksheets("Sheet1").Range("A2:C5")
'Fill the listbox
Set lbtarget = Me.ListBox1
With lbtarget
'Determine number of columns
.ColumnCount = 3
'Set column widths
.ColumnWidths = "50;80;100"
'Insert the range of data supplied
.List = rngSource.Cells.Value
End With
End Sub
Pulling the data into an array first, then placing the array in the listbox
Code:
Private Sub UserForm_Initialize()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: To populate a multi-column listbox with data from
' a worksheet range
Dim lb As msforms.ListBox
Dim rcArray() As Variant
Dim lrw As Long, lcol As Long
Dim rngTarget As Range
'Define the range you want to use
Set rngTarget = Worksheets("Sheet1").Range("A2:C5")
'Set the boundaries of the array
ReDim Preserve rcArray(1 To rngTarget.Rows.Count, 1 To rngTarget.Columns.Count)
'Fill the array with data from the worksheet
With rngTarget
For lcol = 1 To .Columns.Count
For lrw = 1 To .Rows.Count
rcArray(lrw, lcol) = rngTarget.Cells(lrw, lcol)
Next lrw
Next lcol
End With
'Place the array in the listbox
Set lb = Me.ListBox1
With lb
.ColumnCount = 3
.ColumnWidths = "50;80;100"
.List = rcArray
End With
End Sub
Example File:
