VBA ListBox selected index

List Box example

ListBoxes are useful controls, but working with them can be a bit tricky. Before displaying a dialog box that uses a ListBox, fill the ListBox with items. Then, when the dialog box is closed, you need to determine which item[s] the user selected.

When dealing with list boxes, you need to know about the following properties and methods:

1 AddItem: You use this method to add an item to a ListBox.

1 ListCount: This property returns the number of items in the ListBox.

1 ListIndex: This property returns the index number of the selected item or sets the item that's selected [single selections only]. The first item has a ListIndex of 0 [not 1].

1 MultiSelect: This property determines whether the user can select more than one item from the ListBox.

1 RemoveAllItems: Use this method to remove all items from a ListBox.

1 Selected: This property returns an array indicating selected items [applicable only when multiple selections are allowed].

1 Value: This property returns the selected item in a ListBox.

Most of the methods and properties that work with ListBoxes also work with ComboBoxes. Thus, after you have figured out how to handle ListBoxes, you can transfer that knowledge to your work with ComboBoxes.

Filling a list box

For best results, start with an empty workbook. The example in this section assumes the following:

1 You've added a UserForm.

1 The UserForm contains a ListBox control named ListBox!.

1 The UserForm has a CommandButton named OKButton.

i The UserForm has a CommandButton named CancelButton, which has the following event-handler procedure:

Private Sub CancelButton_Click[]

Unload UserForml End Sub

The following procedure is stored in the Initialize procedure for the UserForm:

1. Select your UserForm and press F7 to find this predefined procedure.

The VBE displays the Code window for your form and stands ready for you to input the code for the Click event. [The procedure is UserForm_Click.]

2. Using the Procedure drop-down list at the top of the Code window, choose Initialize.

3. Add the initialization code for the form:

This initialization routine runs automatically whenever your UserForm is loaded. Thus, when you use the Show method for the UserForm, the code is automatically run and your list is populated with 12 items, each added via the AddItem method.

4. Create a VBA module with a small Sub procedure to simply display the dialog box:

Sub ShowList[]

UserForml.Show End Sub

It is not mandatory to use the Initialize event procedure to populate your lists. You could do so in a regular VBA procedure. Using an Initialize event procedure just seems like a natural place to take care of such a mundane [though important] step, however.

Determining the selected item

The preceding code merely displays a dialog box with a ListBox filled with month names. What's missing is a procedure to determine which item in the ListBox is selected.

Add the following to the OKButton_Click procedure:

Private Sub OKButton_Click[] Dim Msg As String Msg = "You selected item # " Msg = Msg & ListBoxl.Listlndex vbNewLine ListBoxl.Value

Msg = Msg & Msg = Msg & MsgBox Msg Unload UserForml Sub

This procedure displays a message box with the selected item number and the selected item. Figure 18-4 shows how this looks.

Figure 18-4:

Determining which item in a ListBox is selected.

Figure 18-4:

Determining which item in a ListBox is selected.

The first item in a ListBox has a ListIndex of 0, not 1 [as you may expect]. This is always the case, even if you use an Option Base 1 statement to change the default lower bound for arrays.

This example is available at this book's Web site.

Determining multiple selections

If your ListBox is set up so the user can select more than one item, you find that the Listlndex property returns only the last item selected. To determine all selected items, you need to use the Selected property, which contains an array.

To allow multiple selections in a ListBox, set the MultiSelect property to either 1 or 2. You can do so at design time by using the Properties window or at run time by using a VBA statement such as this:

UserForml.ListBoxl.MultiSelect = 1

The MultiSelect property has three possible settings. The meaning of each is shown in Table 18-1.

Table 18-1 Settings for the MultiSelect Property

Value VBA Constant

0 fmMultiSelectSingle

1 fmMultiSelectMulti

2 fmMultiSelectExtended

Meaning

Only a single item can be selected.

Clicking an item or pressing the spacebar selects or deselects an item in the list.

Items are added to or removed from the selection set in the traditional manner: holding down the Shift or Ctrl key as you click items.

The following procedure displays a message box that lists all selected items in a ListBox. Figure 18-5 shows an example.

Private Sub OKButton_Click[] Dim Msg As String Dim i As Integer

Msg = "You selected" & vbNewLine For i = 0 To ListBoxl.ListCount - 1 If ListBoxl.Selected[i] Then

Msg = Msg & ListBoxl.List[i] & vbNewLine End If Next i MsgBox Msg Unload UserForml End Sub

This routine uses a For-Next loop to cycle though each item in the ListBox. Notice that the loop starts with item 0 [the first item] and ends with the last item [determined by the value of the ListCount property minus 1]. If an item's Selected property is True, it means that the list item was selected.

This example is available at this book's Web site.

Figure 18-5:

Determining the selected items in a ListBox allows multiple selections.

Figure 18-5:

Determining the selected items in a ListBox allows multiple selections.

Continue reading here: Selecting a range

Was this article helpful?