In a previous post, I discussed populating one-column ActiveX controls with the RowSource property and the AddItem method. This post will discuss populating these controls when you want more than one column. Here’s the range that I am using
To get all three columns in my listbox, I can set the RowSource property to
and be sure to set the ColumnCount property to 3.
To populate multi-column controls with the AddItem method, I use a procedure like this
Private Sub UserForm_Initialize()
Dim cell As Range
Dim Rng As Range
Set Rng = .Range("A2", .Range("A2").End(xlDown))
For Each cell In Rng.Cells
.List(.ListCount - 1, 1) = cell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
This procedure loops through the cells in column A. It uses AddItem to add the column A value to the ListBox. Then it uses the List property to access the other columns. List takes a row and a column argument. The row argument is computed with the ListCount property. Because the rows and columns start at 0, but ListCount doesn’t, I have to subract one to get the right row. Also note that the second column is 1 and the third is 2.
With multi-column controls, the RowSource property starts looking pretty nice. It’s a heck of a lot easier than all this List and ListCount nonsense. Nevertheless, I still use AddItem exclusively, even for multi-column controls.