ListBox Techniques

The ListBox control is extremely versatile, but it can be a bit tricky to work with. This section contains of a number of simple examples that demonstrate common techniques that involve the ListBox control.

Note  

In most cases, the techniques described in this section also work with a ComboBox control.

About the ListBox control

Following are a few points to keep in mind when working with ListBox controls. Examples in the sections that follow demonstrate many of these points:

• The items in a ListBox can be retrieved from a range of cells (specified by the RowSource property), or they can be added by using VBA code (using the AddItem method).

• A ListBox can be set up to allow a single selection or a multiple selection. This is determined by the MultiSelect property.

• If a ListBox is not set up for a multiple selection, the value of the ListBox can be linked to a worksheet cell by using the ControlSource property.

• It's possible to display a ListBox with no items selected (the ListIndex property will be “1 ). However, after an item is selected, the user cannot deselect all items. The exception to this is if the MultiSelect property is True .

• A ListBox can contain multiple columns (controlled by the ColumnCount property) and even a descriptive header (controlled by the ColumnHeads property).

• The vertical height of a ListBox displayed in a UserForm window isn't always the same as the vertical height when the UserForm is actually displayed.

• The items in a ListBox can be displayed either as check boxes (if multiple selection is allowed) or as option buttons (if a single selection is allowed). This is controlled by the ListStyle property.

For complete details on the properties and methods for a ListBox control, consult the Help system.

Adding items to a ListBox 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 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 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.

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 AddItem 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 ListBox 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 .

 For Row = 1 To 12     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 in 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.

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         UserForm1.ListBox1.AddItem Item     Next Item '   Display the count     UserForm1.Label1.Caption = _       "Unique items: " & NoDupes.Count '   Show the UserForm     UserForm1.Show End Sub 

CD-ROM  

This example, named  listbox unique items1.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.

Determining the selected item

The examples in preceding sections merely display a UserForm with a ListBox filled with various items. These procedures omit a key point: how to determine which item or items were selected by the user.

Note  

This discussion assumes a single-selection ListBox object - one whose MultiSelect property is set to .

To determine which item was selected, access the ListBox's Value property. The statement that follows, for example, displays the text of the selected item in ListBox1 .

 MsgBox ListBox1.Value 

If no item is selected, this statement will generate an error.

If you need to know the position of the selected item in the list (rather than the content of that item), you can access the ListBox's ListIndex property. The following example uses a message box to display the item number of the selected ListBox item:

 MsgBox "You selected item #" & ListBox1.ListIndex 

If no item is selected, the ListIndex property will return “1 .

Note  

The numbering of items in a ListBox begins with 0 - not 1. Therefore, the ListIndex of the first item is , and the ListIndex of the last item is equivalent to the value of the ListCount property less 1.

Determining multiple selections in a ListBox

A ListBox's MultiSelect property can be any of three values:

• 0 ( fmMultiSelectSingle ): Only one item can be selected. This is the default setting.

• 1 ( fmMultiSelectMulti ): Pressing the Space key or clicking selects or deselects an item in the list.

• 2 ( fmMultiSelectExtended ): Shift-clicking extends the selection from the previously selected item to the current item. You can also use Shift and one of the arrow keys to extend the selected items.

If the ListBox allows multiple selections (that is, if its MultiSelect property is either 1 or 2 ), trying to access the ListIndex or Value properties will result in an error. Instead, you need to use the Selected property, which returns an array whose first item has an index of . For example, the following statement displays True if the first item in the ListBox list is selected:

 MsgBox ListBox1.Selected(0) 

CD-ROM  

The companion CD-ROM contains a workbook that demonstrates how to identify the selected item(s) in a ListBox. It works for single-selection and multiple-selection ListBoxes. The file is named  listbox selected items.xlsm .

The following code, from the example workbook on the CD-ROM, loops through each item in the ListBox. If the item was selected, it appends the item's text to a variable called Msg . Finally, the names of all the selected items are displayed in a message box.

 Private Sub OKButton_Click()     Msg = ""     For i = 0 To ListBox1.ListCount - 1         If ListBox1.Selected(i) Then _           Msg = Msg & ListBox1.List(i) & vbCrLf     Next i     MsgBox "You selected: " & vbCrLf & Msg     Unload Me End Sub 

Figure 14-10 shows the result when multiple ListBox items are selected.

Figure 14-10: This message box displays a list of items selected in a ListBox.

Multiple lists in a single ListBox

This example demonstrates how to create a ListBox in which the contents change depending on the user's selection from a group of OptionButtons.

Figure 14-11 shows the sample UserForm. The ListBox gets its items from a worksheet range. The procedures that handle the Click event for the OptionButton controls simply set the ListBox's RowSource property to a different range. One of these procedures follows:

Figure 14-11: The contents of this ListBox depend on the OptionButton selected.

 Private Sub obMonths_Click()     ListBox1.RowSource = "Sheet1!Months" End Sub 

Clicking the OptionButton named obMonths changes the RowSource property of the ListBox to use a range named Months on Sheet1 .

CD-ROM  

This example, named  listbox multiple lists.xlsm , is available on the companion CD-ROM.

ListBox item transfer

Some applications require a user to select several items from a list. It's often useful to create a new list of the selected items and display the new list in another ListBox. For an example of this situation, check out the Customization tab of the Excel Options dialog box.

Figure 14-12 shows a dialog box with two ListBoxes. The Add button adds the item selected in the left ListBox to the right ListBox. The Delete button removes the selected item from the list on the right. A check box determines the behavior when a duplicate item is added to the list: Namely, if the Allow Duplicates check box is not marked , a message box appears if the user attempts to add an item that's already on the list.

Figure 14-12: Building a list from another list.

The code for this example is relatively simple. Here's the procedure that is executed when the user clicks the Add button:

 Private Sub AddButton_Click()     If ListBox1.ListIndex = -1 Then Exit Sub     If Not cbDuplicates Then '       See if item already exists         For i = 0 To ListBox2.ListCount - 1             If ListBox1.Value = ListBox2.List(i) Then                 Beep                 Exit Sub             End If         Next i     End If     ListBox2.AddItem ListBox1.Value End Sub 

The code for the Remove button is even simpler:

 Private Sub RemoveButton_Click()     If ListBox2.ListIndex = -1 Then Exit Sub     ListBox2.RemoveItem ListBox2.ListIndex End Sub 

Notice that both of these routines check to make sure that an item is actually selected. If the ListBox's ListIndex property is “1 , no items are selected, and the procedure ends.

This example has two additional procedures that control whether the Remove button is enabled or disabled. These events are triggered when the ListBox is entered (either via a keystroke or a mouse click). The net effect is that the Remove button is enabled only when the user is working in ListBox2 .

 Private Sub ListBox1_Enter()     RemoveButton.Enabled = False End Sub Private Sub ListBox2_Enter()     RemoveButton.Enabled = True End Sub 

CD-ROM  

This example, named  listbox item transfer.xlsm , is available on the companion CD-ROM.

Moving items in a ListBox

Often, the order of items in a list is important. The example in this section demonstrates how to allow the user to move items up or down in a ListBox. The VBE uses this type of technique to let you control the tab order of the items in a UserForm (right-click a UserForm and choose Tab Order from the shortcut menu).

Figure 14-13 shows a dialog box that contains a ListBox and two CommandButtons. Clicking the Move Up button moves the selected item up in the ListBox; clicking the Move Down button moves the selected item down.

Figure 14-13: The buttons allow the user to move items up or down in the ListBox.

CD-ROM  

This example, named  listbox move items.xlsm , is available on the companion CD-ROM.

The event handler procedures for the two CommandButtons follow:

 Private Sub MoveUpButton_Click()     If ListBox1.ListIndex <= 0 Then Exit Sub     NumItems = ListBox1.ListCount     Dim TempList()     ReDim TempList(0 To NumItems - 1) '   Fill array with list box items     For i = 0 To NumItems - 1         TempList(i) = ListBox1.List(i)     Next i '   Selected item     ItemNum = ListBox1.ListIndex '   Exchange items     TempItem = TempList(ItemNum)     TempList(ItemNum) = TempList(ItemNum - 1)     TempList(ItemNum - 1) = TempItem     ListBox1.List = TempList '   Change the list index     ListBox1.ListIndex = ItemNum - 1 End Sub Private Sub MoveDownButton_Click()     If ListBox1.ListIndex = ListBox1.ListCount - 1 Then Exit Sub     NumItems = ListBox1.ListCount     Dim TempList()     ReDim TempList(0 To NumItems - 1) '   Fill array with list box items     For i = 0 To NumItems - 1         TempList(i) = ListBox1.List(i)     Next i '   Selected item     ItemNum = ListBox1.ListIndex '   Exchange items     TempItem = TempList(ItemNum)     TempList(ItemNum) = TempList(ItemNum + 1)     TempList(ItemNum + 1) = TempItem     ListBox1.List = TempList '   Change the list index     ListBox1.ListIndex = ItemNum + 1 End Sub 

These procedures work fairly well, but you'll find that, for some reason, relatively rapid clicking doesn't always register. For example, you may click the Move Down button three times in quick succession, but the item only moves one or two positions . The solution is to add a new DblClick event handler for each CommandButton. These procedures, which simply call the Click procedures, are as follows:

 Private Sub MoveUpButton_DblClick _   (ByVal Cancel As MSForms.ReturnBoolean)     Call MoveUpButton_Click End Sub Private Sub MoveDownButton_DblClick _   (ByVal Cancel As MSForms.ReturnBoolean)     Call MoveDownButton_Click End Sub 

Working with multicolumn ListBox controls

A normal ListBox has a single column for its contained items. You can, however, create a ListBox that displays multiple columns and ( optionally ) column headers. Figure 14-14 shows an example of a multicolumn ListBox that gets its data from a worksheet range.

Figure 14-14: This ListBox displays a three-column list with column headers.

CD-ROM  

This example, named  listbox multicolumn1.xlsm , is available on the companion CD-ROM.

To set up a multicolumn ListBox that uses data stored in a worksheet range, follow these steps:

1. Make sure that the ListBox's ColumnCount property is set to the correct number of columns.

2. Specify the correct multicolumn range in the Excel worksheet as the ListBox's RowSource property.

3. If you want to display column headers, set the ColumnHeads property to True . Do not include the column headings on the worksheet in the range setting for the RowSource property. VBA will instead automatically use the row directly above the first row of the RowSource range.

4. Adjust the column widths by assigning a series of values, specified in points (1/72 of one inch) and separated by semicolons, to the ColumnWidths property. For example, for a three-column list box, the ColumnWidths property might be set to the following text string:

    100;40;30 

5. Specify the appropriate column as the BoundColumn property. The bound column specifies which column is referenced when an instruction polls the ListBox's Value property.

To fill a ListBox with multicolumn data without using a range, you first create a two-dimensional array and then assign the array to the ListBox's List property. The following statements demonstrate this using a 12-row — 2-column array named Data . The two-column ListBox shows the month names in column 1 and the number of the days in the month in column 2 (see Figure 14-15). Notice that the procedure sets the ColumnCount property to 2 .

Figure 14-15: A two-column ListBox filled with data stored in an array.

 Private Sub UserForm_Initialize() '   Fill the list box     Dim Data(1 To 12, 1 To 2)     For i = 1 To 12         Data(i, 1) = Format(DateSerial(2007, i, 1), "mmmm")     Next i     For i = 1 To 12         Data(i, 2) = Day(DateSerial(2007, i + 1, 1) - 1)     Next i     ListBox1.ColumnCount = 2     ListBox1.List = Data End Sub 

CD-ROM  

This example is available on the companion CD-ROM. The file is named  listbox multicolumn2.xlsm .

Note  

There appears to be no way to specify column headers for the ColumnHeads property when the list source is a VBA array.

Using a ListBox to select worksheet rows

The example in this section displays a ListBox that consists of the entire used range of the active worksheet (see Figure 14-16). The user can select multiple items in the ListBox. Clicking the All button selects all items, and clicking the None button deselects all items. Clicking OK selects those corresponding rows in the worksheet. You can, of course, select multiple noncontiguous rows directly in the worksheet by pressing Ctrl while you click the row borders. However, you might find that selecting rows is easier when using this method.

Figure 14-16: This ListBox makes it easy to select rows in a worksheet.

CD-ROM  

This example, named  listbox select rows.xlsm , is available on the companion CD-ROM.

Selecting multiple items is possible because the ListBox's MultiSelect property is set to 1 - fmMultiSelectMulti . The check boxes on each item are displayed because the ListBox's ListStyle property is set to 1 - fmListStyleOption .

The UserForm's Initialize procedure follows. This procedure creates a Range object named rng that consists of the active sheet's used range. Additional code sets the ListBox's ColumnCount and RowSource properties and adjusts the ColumnWidths property so that the ListBox columns are proportional to the column widths in the worksheet.

 Private Sub UserForm_Initialize()     Dim ColCnt As Integer     Dim rng As Range     Dim cw As String     Dim c As Integer     ColCnt = ActiveSheet.UsedRange.Columns.Count     Set rng = ActiveSheet.UsedRange     With ListBox1         .ColumnCount = ColCnt         .RowSource = rng.Address         cw = ""         For c = 1 To .ColumnCount             cw = cw & rng.Columns(c).Width & ";"         Next c         .ColumnWidths = cw         .ListIndex = 0     End With End Sub 

The All and None buttons (named SelectAllButton and SelectNoneButton , respectively) have simple event handler procedures as follows:

 Private Sub SelectAllButton_Click()     Dim r As Integer     For r = 0 To ListBox1.ListCount - 1         ListBox1.Selected(r) = True     Next r End Sub Private Sub SelectNoneButton_Click()     Dim r As Integer     For r = 0 To ListBox1.ListCount - 1         ListBox1.Selected(r) = False     Next r End Sub 

The OKButton_Click procedure follows. This procedure creates a Range object named RowRange that consists of the rows that correspond to the selected items in the ListBox. To determine whether a row was selected, the code examines the Selected property of the ListBox control. Notice that it uses the Union function to add ranges to the RowRange object.

 Private Sub OKButton_Click()     Dim RowRange As Range     RowCnt = 0     For r = 0 To ListBox1.ListCount - 1         If ListBox1.Selected(r) Then             RowCnt = RowCnt + 1             If RowCnt = 1 Then                 Set RowRange = ActiveSheet.UsedRange.Rows(r + 1)             Else                 Set RowRange = _                   Union(RowRange, ActiveSheet.UsedRange.Rows(r + 1))             End If         End If     Next r     If Not RowRange Is Nothing Then RowRange.Select     Unload Me End Sub 

CD-ROM  

This example is available on the companion CD-ROM. The file is named  listbox select rows.xlsm .

Using a ListBox to activate a sheet

The example in this section is just as useful as it is instructive. This example uses a multicolumn ListBox to display a list of sheets within the active workbook. The columns represent

• The sheet's name

• The type of sheet (worksheet, chart, or Excel 5/95 dialog sheet)

• The number of nonempty cells in the sheet

• Whether the sheet is visible

Figure 14-17 shows an example of the dialog box.

Figure 14-17: This dialog box lets the user activate a sheet.

The code in the UserForm_Initialize procedure (which follows) creates a two-dimensional array and collects the information by looping through the sheets in the active workbook. It then transfers this array to the ListBox.

 Public OriginalSheet As Object Private Sub UserForm_Initialize()     Dim SheetData() As String     Set OriginalSheet = ActiveSheet     ShtCnt = ActiveWorkbook.Sheets.Count     ReDim SheetData(1 To ShtCnt, 1 To 4)     ShtNum = 1     For Each Sht In ActiveWorkbook.Sheets         If Sht.Name = ActiveSheet.Name Then _           ListPos = ShtNum - 1         SheetData(ShtNum, 1) = Sht.Name         Select Case TypeName(Sht)             Case "Worksheet"                 SheetData(ShtNum, 2) = "Sheet"                 SheetData(ShtNum, 3) = _                   Application.CountA(Sht.Cells)             Case "Chart"                 SheetData(ShtNum, 2) = "Chart"                 SheetData(ShtNum, 3) = "N/A"             Case "DialogSheet"                 SheetData(ShtNum, 2) = "Dialog"                 SheetData(ShtNum, 3) = "N/A"         End Select         If Sht.Visible Then             SheetData(ShtNum, 4) = "True"         Else              SheetData(ShtNum, 4) = "False"         End If         ShtNum = ShtNum + 1     Next Sht     With ListBox1         .ColumnWidths = "100 pt;30 pt;40 pt;50 pt"         .List = SheetData         .ListIndex = ListPos     End With End Sub 

The ListBox1_Click procedure follows:

 Private Sub ListBox1_Click()     If cbPreview Then _         Sheets(ListBox1.Value).Activate End Sub 

The value of the CheckBox control (named cbPreview ) determines whether the selected sheet is previewed when the user clicks an item in the ListBox.

Clicking the OK button (named OKButton ) executes the OKButton_Click procedure, which follows:

 Private Sub OKButton_Click()     Dim UserSheet As Object     Set UserSheet = Sheets(ListBox1.Value)     If UserSheet.Visible Then         UserSheet.Activate     Else         If MsgBox("Unhide sheet?", _           vbQuestion + vbYesNoCancel) = vbYes Then             UserSheet.Visible = True             UserSheet.Activate         Else             OriginalSheet.Activate         End If     End If     Unload Me End Sub 

The OKButton_Click procedure creates an object variable that represents the selected sheet. If the sheet is visible, it is activated. If it's not visible, the user is presented with a message box asking whether it should be unhidden. If the user responds in the affirmative , the sheet is unhidden and activated. Otherwise, the original sheet (stored in a public object variable named OriginalSheet ) is activated.

Double-clicking an item in the ListBox has the same result as clicking the OK button. The ListBox1_DblClick procedure, which follows, simply calls the OKButton_Click procedure.

 Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)     Call OKButton_Click End Sub 

CD-ROM  

This example is available on the companion CD-ROM. The file is named  listbox activate sheet.xlsm .