ListBox with Excel VBA

The ListBox control is used to store a list of values. In the list, the user can select one or more values, which are then used in the program.

Note that at design time, a list visually resembles a text box. Typically, selecting an item from the list is done by clicking on it. A double-click on an item, however, is often used to perform certain actions in the program associated with that item.

Item-by-item population of a list
A list can be populated item by item using the AddItem method.

As an example, let us create a simple project), which contains a form with a list. City names are added to the list. Selecting an item from the list does nothing, but double-clicking on an item outputs it into the next free cell of the first column of the active worksheet.

The selected value from the list is returned by the Text property. The number of filled cells in the column is returned by the CountA property of the WorksheetFunction object.

Populating a list from an array and selecting an operation
A list can be populated item by item or in one operation. To do this, assign the List property of the ListBox to a variable that contains an array of values.

As an example, let us once again return to the project from the section “Addition of two numbers” earlier in this chapter.
This time, instead of calculating the sum of two numbers, we will perform one of four arithmetic operations listed in the list.

So, create a form that contains three labels, three text boxes, a button, and a list.
For all controls except the list, assign property values using the Properties window as described in the section “Addition of two numbers”.
For the list, assign the Name property the value lstOp.

In the form module, type the required code.

An item in the list is identified by its index, which is returned by the ListIndex property. Indexing starts from 0.
In this code, the selected item (i.e., the operation) is displayed in the form’s title bar. This is achieved by handling the Click event of the list.

Populating a list from a range
The RowSource property allows a list to be populated from a range. The value of this property is a string containing the name of the range.

Let us demonstrate the use of this property with an example, considering both the case when the range is known in advance and the case when only a single cell of the range is known.

We will create a simple project for recording business trips of employees of a certain company. The workbook contains three worksheets: Cities, Employees, and Trips.

• On the Cities sheet, the range A1:A5 contains the list of cities where employees are scheduled to travel.
• On the Employees sheet, column A starting from cell A1 contains the list of employee names. The size of this list is not fixed and may change depending on the user.
• On the Trips sheet, two columns display the list of employee trips by cities.

The list on the Trips sheet is populated using the Populate list from array window.

This window contains two lists (with employee names and city names) and a button. The lists are populated from the ranges of the corresponding worksheets.

Clicking the OK button enters the selected data into the next free row of the trip list on the Trips sheet.

Create a form containing two lists and a button, and use the Properties window to assign their values as shown in Table.

Table. Property values set in the Properties window

In the form module, type the required code.

When the form initializes, the lists are populated.
The city list is filled by explicitly specifying the range address.

Before populating the employee list—since its size may vary—the program first identifies the range, then determines its address, and only then fills the list.

When the OK button is clicked, the program first checks whether data has been selected from the lists, then determines the first free row in the list on the Trips sheet, and outputs the selected data from the form into that row.

Selecting multiple items from a list
In a list, it is possible to select either a single item or multiple items. The MultiSelect property defines the mode in which such selection is allowed.

The valid values of the MultiSelect property are the following constants:

• fmMultiSelectSingle — only one item can be selected.
• fmMultiSelectMulti — clicking an item or pressing the key selects or deselects the item.
• fmMultiSelectExtended — clicking an item while holding the key selects or deselects the item. Clicking an item while holding the key selects a range of items from the previously selected one to the current one.

The Selected property is used to identify the selected items. If its value is True, the item is selected; if False, it is not selected. The List property returns the item at the specified index, remembering that indexing starts at 0. The total number of items in the list is returned by the ListCount property.

As a demonstration, let us create a project in which multiple items can be selected from a list. Create a form with a list and a button, and using the Properties window, set their values as shown in Table.

Table. Property values set in the Properties window

When the form initializes, the list is populated and the mode allowing multiple selection is set. Clicking the OK button then sequentially reads the selected items and composes an information string, which is displayed in a message box.

Coordinated operation of two lists
It is often necessary to coordinate the operation of two lists. For example, a publishing house may cooperate with certain stores in different cities. One list displays a list of cities. When an item is selected from this list, the second list displays the list of stores.

How can this be done? Very simply. Array elements can have the Variant type, and a Variant variable can hold anything, including another array. Therefore, in VBA it is possible to create an array of arrays

Multi-column lists
Lists can be not only single-column but also multi-column. To create a multi-column list, set the ColumnCount property to the desired number of columns.

In addition, the ColumnWidths property can be used to set the width of each column in the list.

ColumnWidths = String

Here, String is a text string consisting of numbers representing the widths of the corresponding columns, separated by semicolons. For example, the string « 90;80 » means that 90 points are allocated to the first column and 80 to the second.

Access to list elements is provided by the List property, where the first parameter is the row index and the second is the column index. Both rows and columns are indexed starting at 0:

List(row, column)

As an example, let us create a demo project in which a two-column list displays a given number of random numbers. Create a form with a list, a text box, and a button, and using the Properties window, set their values as shown in Table.

When the form initializes, the list is set to two columns, with 30 points allocated to the first column and 50 to the second. When the OK button is clicked, the list is first cleared with the Clear method and then populated with the specified number of random numbers and their indexes. Selecting an item in the list displays a message box with information about the chosen value.

Table. Property values set in the Properties window

Populating a multi-column list from a range and calculating the average value of selected numbers
Like a single-column list, a multi-column list can also be populated from a data range.

As an example, let us build an application that calculates the average grade of a selected group of students from a list. The list is populated from a range with its top-left corner at cell A1. The first column contains student names, and the second contains their grades.

Create a form with a label, a text box, a list, and a button. In the form module, enter the necessary code. At form initialization, the list is populated and the Caption properties of the controls and form are set.

When the OK button is pressed, it first checks whether any items have been selected. This is necessary because the average grade equals the sum of the grades divided by their count. If no items are selected, the formula would cause a division-by-zero error.

Hiding data in a multi-column list
If any component in the ColumnWidths property is set to zero, the corresponding column is hidden. This can be used to hide auxiliary information.

For example, let us create a demo project called “Human Resources.” In it, a list is filled from range A2:C9 of the Employees worksheet with information about company employees: surname, position, and years of service (Fig. 4.39). Although all this data is loaded into the list, only the surnames are displayed.

When an item is selected, a message box displays the employee’s surname. By setting the required checkboxes (Position and Experience), additional information about the employee can be shown.

To implement this project, create a workbook with a worksheet named Employees and enter employee data into range A2:C9. Create a form with a list and two checkboxes. In the form module, enter the code.

At form initialization, the list is populated and the Caption values of controls and form are set. When an item is selected, a message string is generated depending on the state of the checkboxes and displayed in a message box.

The first column element is returned by the Text property, while the second and third columns are returned by the List property.

Changing the state of a checkbox also triggers the list’s Click event procedure, provided there are selected items. This is checked by the value returned by ListIndex. If no item is selected, ListIndex returns –1.

Displaying selected values from a multi-column list using the Text and Value properties
By default, the Text property returns the selected element from the first column. However, this can be changed using the TextColumn property, which specifies the column whose selected element is returned by Text.

In addition to Text, the list also has a Value property that works similarly. The column from which it returns a value is defined by the BoundColumn property. Note that in both TextColumn and BoundColumn, column numbering starts at 1.

The following code is a modification of the previous project, where now the values from the second and third columns are read using the Text and Value properties. Since the list contains three columns (with the last two hidden), the value from the first column is read using the List property.

Displaying selected values from a multi-column list using the Text and Value properties

Private Sub UserForm_Initialize()
    ListBox1.ColumnCount = 3
    ListBox1.ColumnWidths = "80;0;0"
    Me.Caption = "Human Resources"
    ListBox1.RowSource = "Employees!A2:C9"
    CheckBox1.Caption = "Position"
    CheckBox2.Caption = "Experience"
    ListBox1.TextColumn = 2
    ListBox1.BoundColumn = 3
End Sub

Private Sub ListBox1_Click()
    Dim msg As String
    msg = "Last Name: " & ListBox1.List(ListBox1.ListIndex, 0) & vbCr
    If CheckBox1.Value Then
        msg = msg & "Position: " & ListBox1.Text & vbCr
    End If
    If CheckBox2.Value Then
        msg = msg & "Expérience: " & ListBox1.Value & vbCr
    End If
    MsgBox msg
End Sub

Dragging items from one list to another
The DataObject object can serve as a data carrier when programming a drag operation. Its methods allow you to control the drag process from start to finish.

Table. Methods of the DataObject object

When coding a drag operation, you must handle two events:

• BeforeDragOver — triggered during the drag operation.
• BeforeDropOrPaste — triggered immediately before the object is dropped.

To demonstrate how a drag operation can be programmed, create a form, place two lists on it, and enter the corresponding codeD). The project is ready.