Adding items to a List Box control
Before displaying a UserForm that uses a ListBox control, you'll probably need to fill the ListBox with items. You can fill a ListBox at design time using items stored in a worksheet range or at runtime, using VBA to add the items to the ListBox. The two examples in this section presume that
You have a UserForm named UserForml.
This UserForm contains a ListBox control named ListBoxl.
The workbook contains a sheet named Sheetl, and range A1:A12 contains the items to be displayed in the ListBox.
ADDING ITEMS TO A LISTBOX AT DESIGN TIME
To add items to a ListBox at design time, the ListBox items must be stored in a worksheet range. Use the RowSource property to specify the range that contains the ListBox items. Figure 14-8 shows the Properties window for a ListBox control. The RowSource property is set to Sheet1!A1:A12. When the UserForm is displayed, the ListBox will contain the 12 items in this range. The items appear in the ListBox at design time as soon as you specify the range for the RowSource property.
Make sure that you include the worksheet name when you specify the RowSource property;otherwise,the ListBox will use the specified range on the active worksheet. In some cases, you might need to fully qualify the range by including the workbook name. For example:
[Book1.xls]Sheet1!A1:A12
A better practice is to define a name for the range and use that name in your code.This will ensure that the proper range is used even if rows above the range are added or deleted.
ADDING ITEMS TO A LISTBOX AT RUNTIME
To add ListBox items at runtime, you have two choices:
Set the RowSource property to a range address by using code.
Write code that uses the Addltem method to add the ListBox items.
As you might expect, you can set the RowSource property via code rather than with the Properties window. For example, the following procedure sets the RowSource property for a ListBox before displaying the UserForm. In this case, the items consist of the cell entries in a range named Categories on the Budget worksheet.
UserForml.ListBoxl.RowSource = "Budget!Categories" UserForml.Show
If the ListBox items are not contained in a worksheet range, you can write VBA code to fill the ListBox before the dialog box appears. The procedure fills the ListBox with the names of the months by using the Addltem method.
Sub ShowUserForm2[] ' Fill the list box
With UserForm2.ListBox1 .RowSource="" .Addltem "January"
.Addltem "February"
.Addltem "March"
.Addltem "April"
.Addltem "May"
.Addltem "June"
.Addltem "July"
.Addltem "August"
.Addltem "September"
.Addltem "October"
.Addltem "November"
.Addltem "December" End With UserForm2.Show End Sub
In the preceding code, notice that I set the RowSource property to an empty string. This is to avoid a potential error that occurs if the Properties window has a nonempty RowSource setting. If you try to add items to a ListBox that has a non-null RowSource setting, you'll get a "permission denied" error.
You can also use the Addltem method to retrieve ListBox items from a range. Here's an example that fills a ListBox with the contents of A1:A12 on Sheetl.
UserForml.ListBoxl.Addltem Sheets["Sheet1"].Cells[Row, 1] Next Row
If your data is stored in a one-dimensional array, you can assign the array to the ListBox with a single instruction. For example, assume that you have an array named dData that contains 50 elements. The following statement will create a 50-item list in ListBoxl:
ListBoxl.List = dData
ADDING ONLY UNIQUE ITEMS TO A LISTBOX
In some cases, you might need to fill a ListBox with unique [nonduplicated] items from a list. For example, assume you have a worksheet that contains customer data. One of the columns might contain the state [see Figure 14-9]. You would like to fill a ListBox with the state name of your customers, but you don't want to include duplicate state names.
One technique involves using a Collection object. You can add items to a Collection object with the following syntax:
object.Add item, key, before, after
The key argument, if used, must be a unique text string that specifies a separate key that can be used to access a member of the collection. The important word here is unique. If you attempt to add a nonunique key to a collection, an error occurs, and the item is not added. You can take advantage of this situation and use it to create a collection that consists only of unique items.
The following procedure demonstrates: It starts by declaring a new Collection object named NoDupes. It assumes that a range named Data contains a list of items, some of which may be duplicated.
The code loops through the cells in the range and attempts to add the cell's value to the NoDupes collection. It also uses the cell's value [converted to a string] for the key argument. Using the On Error Resume Next statement causes VBA to ignore the error that occurs if the key is not unique. When an error occurs, the item is not added to the collection which is just what you want. The procedure then transfers the items in the NoDupes collection to the ListBox. The UserForm also contains a label that displays the number of unique items.
Sub RemoveDuplicates1[]
Dim AllCells As Range, Cell As Range Dim NoDupes As New Collection
On Error Resume Next
For Each Cell In Range["Data"]
NoDupes.Add Cell.Value, CStr[Cell.Value] Next Cell On Error GoTo 0
' Add the non-duplicated items to a ListBox For Each Item In NoDupes
UserForm1.ListBox1.AddItem Item Next Item
' Display the count
UserForm1.Label1.Caption = _
"Unique items: " & NoDupes.Count
' Show the UserForm
UserForm1.Show End Sub
This workbook,along with a slightly more sophisticated version of this example,is available on the CD-ROM.
Continue reading here: Determining the selected item
Was this article helpful?
+4 -5