VBA, Multi Column ListBoxes
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:
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:
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:
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:
Modify Listbox Data:
The items in a multi column listbox can be accessed using themember. This member accepts two parameters as input:
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).
Lets say we have a listbox with the following data:
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:
Step 2, Modify the new row:
Using the method explained in the previous section the fields of the new row are be modified.
The code below will do the follwing:
- Change to a 4 column listbox.
- Modify its column widths
- Add values to the 4 columns.
Get Selected Items, From Columns:
In order to determine the selected items in a listbox, theproperty of the list box could be used:
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 theproperty to determine if the current row is selected or not.
After determining if the items is selected or not, you can use themember to get the value from the desired column.
Lets say we have the userform below:
Assume the following values have been selected in the list box:
You can download the file and code related to this article from the links below: