Microsoft Office Tutorials and References
In Depth Information
Using the List property is even simpler. The statement that follows has the same effect as the
preceding For Next loop.
UserForm1.ListBox1.List = Application.Transpose(Sheets(“Sheet1”). _
Note that I used the Transpose function because the List property expects a horizontal array
and the range is in a column rather than a row.
You can also use the List property if your data is stored in a one-dimensional array. For
example, assume that you have an array named MyList that contains 50 elements. The following
statement will create a 50-item list in ListBox1 :
UserForm1.ListBox1.List = MyList
The examples in this section are available on the companion CD-ROM. The file is named
listbox fill.xlsm .
Adding only unique items to a ListBox
In some cases, you may need to fill a ListBox with unique (nonduplicated) items from a list. For
example, assume that you have a worksheet that contains customer data. One of the columns
might contain the state (see Figure 14-9). You’d like to fill a ListBox with the state names of your
customers, but you don’t want to include duplicate state names.
One technique involves using a Collection object. After creating a new Collection object,
you can add items to the 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 you
can use to access a member of the collection. The important word here is unique. If you attempt
to add a non-unique key to a collection, an error occurs, and the item isn’t added. You can take
advantage of this situation and use it to create a collection that consists only of unique items.
The following procedure 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.