Microsoft Office Tutorials and References
In Depth Information
ListBox Techniques
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.
Search JabSto ::

Custom Search