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. Yo 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 userForm1 .

■ This UserForm contains a ListBox control named ListBox1 .

■ The workbook contains a sheet named Sheet1 , and range A1:A12 contains the items to be displayed in th 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.

Figure 14-8: Setting the RowSource property at design time. Caution

In most cases, you'll want to 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:

[budget.xlsx]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 AddItem 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.

UserForm1.ListBox1.RowSource = "Budget ! Categories" UserForm1.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 following procedure fills the ListBox with the names of the months by using the AddIte method.

Sub ShowUserForm2() ' Fill the list box

With UserForm2.ListBox1 .RowSource="" .AddItem "January" .AddItem "February" .AddItem "March" .AddItem "April" .AddItem "May" .AddItem "June" .AddItem "July" .AddItem "August" .AddItem "September" .AddItem "October" .AddItem "November" .AddItem "December" End With UserForm2.Show End Sub

Caution

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 ListBo: that has a non-null RowSource setting, you'll get a "permission denied" error.

You can also use the AddItem method to retrieve ListBox items from a range. Here's an example that fills a ListBox with the contents of A1:A12 on Sheet1 .

UserForm1.ListBox1.AddItem Sheets("Sheet1").Cells(Row, 1) Next Row

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") .Range("A1: A12"))

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 ii

ListBox1 :

UserForm1.ListBox1.List = MyList

CD-ROM

The examples in this section are available on the companion CD-ROM. The file is named ® fill listbox.xlsm .

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.

1

CHlOmt ID

yjic

a

1001

CA

S^tafi Afi irom

m

3

una

OH

4

lJOOJ

FL

Lh« -imi-

3

1004

MV

d

a

1001

?_

■■

1006

PA

1»-

-1

B

1007

CA

ru PI

9

10OB

It.

L

jd

« 1

ID

1005

AZ

M

11

1010

ltd

If

1011

CA

13

1012

FL

14

1013

NV

»

1014

NJ

l(j

UBS

IT

101«

Cfc

IS

1017

HT

IS

10 IE

11

ZD

1019

MD

■t

> w Shoctl '

*

Figure 14-9: A Collection object is used to fill a ListBox with the unique items from column B.

Figure 14-9: A Collection object is used to fill a ListBox with the unique items from column B.

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 non-unique 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 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("State")

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

UserForml.ListBoxl.Addltem Item Next Item

' Display the count

UserForml.Labell.Caption = _

"Unique items: " & NoDupes.Count

' Show the UserForm

UserForml.Show End Sub

CD-ROM

This example, named listbox unique itemsl.xlsm , is available on the companion CD-ROM. A workbook named listbox unique items2.xlsm has a slightly more sophisticated version of this technique and displays the items sorted.

+1 0

Responses

Post a comment