Listboxes on userforms with Excel VBA macros
On this page: Static RowSource Dynamic RowSource The AddItem method Using arrays Preselect items No duplicates The user's selection |
Listboxes are often used as controls on Userforms. They show a list of items or values, and then the user can pick one or more. What the user does, determines what to do next.
This page shows examples on how to fill and handle listboxes on your own userforms. It also shows how to preselect items on the list. To test the code, highlight it with the mouse, copy [CTRL+C] and paste [CTRL+V] into the userforms code.
The procedures are pretty much the same for ComboBoxes, so I'll skip comboboxes or cover them some other time.
If you want to play along as we proceed, now is a good time to open Excel and the VBA editor [ALT+F11] and insert a new Userform. Add a listbox and a command button. Click on the userform and press F7 to open the code window. Then you are ready.
How to fill a ListBox
A listbox can be filled by using cells in the worksheet as source - the list's Rowsource - or by adding items one by one with the AddItem method.
If the items are dates, it can be tricky to display them in the desired format - read more about this on Date format in a ComboBox or ListBox.
RowSource
One of a listbox' properties is "RowSource", which is the address of a range in the spreadsheet, e.g. "Sheet1!A1:A15".
The list's RowSource can be written directly in the listbox' property window [press F4 if it isnt visible], or you can define the range in the userform's Initialize procedure.
It is important to know that RowSource must be of the data type String [text that is], and that it points to the active sheet if nothing else is specified.
If you define RowSource in the userform's Initialize procedure, it could look like this:
You can also use a named range. If for instance the range is named "spring" it will look like this:
Notice that we need ".Address" to get a String.
Dynamic range as RowSource
The procedure above works fine, if it is always the same range you use as rowsource, but what if it changes from time to time?
Then you must use a dynamic range as rowsource in the Initialize procedure. In the following example we find the number of rows down to the first empty cell and use it as rowsource.
Fill the list with AddItem
Instead of defining a rowsource in the spreadsheet you can add the items one by one with the AddItem method. You can do it manually or with a loop, where you read from a range, a collection or the like.
First an example of how to do it manually:
That was dead easy. You could do the same with a loop:
You can also loop through a range and use the AddItem method. It is slower than using the range's address as rowsource, but it gives you more freedom to be picky.
In the following example we loop through a dynamic range in column A. Imagine that the cells contain a mix of text, dates and numbers, and you want the dates only.
Using an array as listbox source
When you use the AddItem method, you can also use an array as source, however that is unnecessary, because you can set the list = an array in one operation - not unlike the RowSource method.
The following procedure fills an array with the numbers 1 to 100 and then populates the listbox - it is quite simple.
The list can have more than one column. In the following example we fill an array with 2 columns and use it as source for a listbox list.
A ListBox with unique items
If you want a list without duplicates [and maybe even in alphabetic order] with a range as source, you start by making a collection as described near the bottom of the page How to make your own collections in VBA Excel [advanced collection].
Imagine that we have named our collection "colList". Then we make our list like below. To make the code work you must declare colList as a collection and write the procedure [here: "MakeCollection"] that makes our collection.
How to preselect items in a Listbox
You may want to preselect items in a listbox. There can be many reasons, but one could be to indicate, which items are already in use and that they can be deselected.
You can do that by looping through the list and set the items' Selected property = True. In the following example we insert the numbers from 1 to 10, and if the number divided by 2 leaves 0 [zero], we preselect it.
The example above would look like this, when the form opens:
If you have an array or a range with True/False or numeric values, you can also set the item's Selected property = the value in your array/range. "True" will preselect the item, and so will any number different from zero.
It could look like this, where rRange is a range with True/False or numbers in the cells:
When the user has selected from the list
When the user has selected from the list, we must find out what he selected. Here it is decisive whether the listbox is set up for one choice or multiple. That is defined in the ListBox properties window in "MultiSelect", but the property can also be set at runtime with:
If the user can select only one thing, it is easy. Then you find the selection with:
If the user can select multiple items, you cannot use ListBox1.Value. Instead you need the ListBox' Selected property.
Here is an example with a command button, where we loop through the list, and the selected items are inserted in cell B1 and down.
Notice the "-1" in the first line of the loop:
That is because the first item isn't number 1, but "0" - zero like in arrays. If for instance you have 10 items on your list, "ListBox1.Count" will return the number "10", but if you loop the list from zero, you will be at item nb. 10 when you reach 9.
Try it yourself, if you have got 10 fingers. :-]
Related:
- Inputboxes and userforms
- Dependent comboboxes, arrays in class collection
- Date format in a ComboBox or ListBox
- Textboxes on userforms
- Loop control elements on a userform
- Arrays
- Collections
- Excel VBA sitemap