In this article I will explain how you can work with a listbox with multiple columns. In the figure below you can see an example of what a multi column listbox would look like:
Creating Multi Column Listboxes:
There are several methods for creating a multi column listbox.
Method 1, Using the property window:
After inserting a listbox onto the userform, you can define the number of columns using the property window. Change the Column Count Property to the number of columns you wish to have:
Method 2, Through VBA Code:
If you don’t know the number of columns you will be needing before runtime, you can set the number of columns using VBA through the ColumnCount property. The code below will create 3 columns for our listbox:
ListBox1.ColumnCount = 3
Modifying Column Width:
Again there are several methods for modifying the width of the columns:
Method 1, using the property window:
In this method the column widths are defined by modifying the ColumnWidths property in the property windows. The width of each column is separated using a semicolon. For a listbox with 3 columns, the expression below would change the width of the leftmost column to 30 and the middle column to 20. Note that the last column will take up any space that is left. The values are from left to right:
30;20
Note: When working in Excel, the width unit will be in points while in access it will be in inches.
Method 2, Through VBA Code:
Another method for changing the column widths is using VBA at runtime. This is specially useful when you don’t know the size of your columns before running the code. The columns widths can be changed using the ColumnWidths property. The code below will change the width of the left most column to 30 and the next column to 20. The last column will always take up whatever space there is left:
ListBox1.ColumnWidths = "30;20"
Modify Listbox Data:
The items in a multi column listbox can be accessed using the .List member. This member accepts two parameters as input:
ListBox1.List[RowIndex, ColumnIndex]
RowIndex: The row index of the record we want to access. Note the rows in a listbox are zero based. Therefor the index of the first row is “0” [zero].
ColumnIndex: The column index of the field we want to access. Note the columns in a multi column listbox are also zero indexed. Therefore the first column has an index of “0” [zero].
Example:
Lets say we have a listbox with the following data:
The code below will change the value in the first column of the second row to “NEWNAME”:
ListBox1.List[1, 0] = "NEWNAME"
Result:
The row “Allen Mathews 478-4578” was changed to “NEWNAME Mathews 478-4578”:
Filling a Multi Column Listbox With Data:
In order to fill a multi column listbox with data there are 2 steps:
Step 1, Create a new row:
This can be done using the code below:
ListBox1.AddItem
Step 2, Modify the new row:
Using the method explained in the previous section the fields of the new row are be modified.
Example:
The code below will do the follwing:
- Change ListBox1 to a 4 column listbox.
- Modify its column widths
- Add values to the 4 columns.
Sub main[]
Dim i As Integer
'define 4 column listbox
ListBox1.ColumnCount = 4
'change column widhts
ListBox1.ColumnWidths = "20;40;60"
'assing values to the columns
For i = 1 To 9
ListBox1.AddItem
ListBox1.List[i - 1, 0] = i
ListBox1.List[i - 1, 1] = i * 10
ListBox1.List[i - 1, 2] = i * 100
ListBox1.List[i - 1, 3] = i * 1000
Next i
End Sub
Result:
Get Selected Items, From Columns:
In order to determine the selected items in a listbox, the .Selected property of the list box could be used:
ListBox1.Selected[RowIndex]
The expression above returns true if the user has selected the row with the index “RowIndex”. One way to determine the selected indices is to loop through all the rows, and use the .Selected property to determine if the current row is selected or not.
After determining if the items is selected or not, you can use the .List member to get the value from the desired column.
Example:
Lets say we have the userform below:
The function below will store the values in the second column of the selected rows in the array, arrLastNames:
Sub Example2[]
Dim arrLastName[1 To 100] As String
Dim i As Integer
Dim counter As Integer
counter = 1
'loop through the values in the listbox
For i = 1 To ListBox1.ListCount
'check if the current value is selected
If ListBox1.Selected[i - 1] = True Then
'add the value in the last name column to the
'array
arrLastName[counter] = ListBox1.List[i - 1, 1]
counter = counter + 1
End If
Next i
End Sub
Assume the following values have been selected in the list box:
Result:
You can download the file and code related to this article from the links below:
- Listbox_GetSelected.xlsm
- ListBox_Fill.xlsm
See also:
- ListBox Object Members [MSDN]
If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com
Tagged with: Listbox, Multi Column, VBA