Fill a ListView on a UserForm
The following code fills a ListView on a UserForm with data from an Excel worksheet. It is assumed that you've already created a UserForm that contains a ListView. It is also assumed that Sheet1 contains the data, starting at A1, and that the first row contains the column headers.
Private Sub UserForm_Activate()
'Set a reference to Microsoft Windows Common Controls by
'using Tools > References in the Visual Basic Editor (Alt+F11)
'Set some of the properties for the ListView
With Me.ListView1
.Gridlines = True
.HideColumnHeaders = False
.View = lvwReport
End With
'Call the sub to fill the ListView
Call LoadListView
End Sub
Private Sub LoadListView()
'Declare the variables
Dim wksSource As Worksheet
Dim rngData As Range
Dim rngCell As Range
Dim LstItem As ListItem
Dim RowCount As Long
Dim ColCount As Long
Dim i As Long
Dim j As Long
'Set the source worksheet
Set wksSource = Worksheets("Sheet1")
'Set the source range
Set rngData = wksSource.Range("A1").CurrentRegion
'Add the column headers
For Each rngCell In rngData.Rows(1).Cells
Me.ListView1.ColumnHeaders.Add Text:=rngCell.Value, Width:=90
Next rngCell
'Count the number of rows in the source range
RowCount = rngData.Rows.Count
'Count the number of columns in the source range
ColCount = rngData.Columns.Count
'Fill the ListView
For i = 2 To RowCount
Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
For j = 2 To ColCount
LstItem.ListSubItems.Add Text:=rngData(i, j).Value
Next j
Next i
End Sub
Where to Put the Code
- Open the workbook in which to store the code.
- Open the Visual Basic Editor (Alt+F11).
- In the Project Explorer window (Ctrl+R), right-click the UserForm, and select View Code.
- Copy and paste the above code into the code module for the UserForm.
- Return to Microsoft Excel (Alt+Q).
- Save the workbook.
Sample Workbook: Download