ListBox

The ListBox control displays a list of values and lets a user select one or more. The ListBox can either appear as a list or as a group of OptionButton controls or CheckBox controls. The ListBox has similar properties and events as the ComboBox or TextBox. It also has the same two methods of the ComboBox to add and remove items. The toolbox symbol for a ListBox control is highlighted in the image below.

 

ListBox Properties

As with other controls, the Name property is used as an object to target any other property or method. We can change the default name of the ListBox control in the properties window. We can change the color, borders, font size and style, position, dimensions of the box, and other properties as done with other controls.

One of the distinctive properties of the ListBox control is the property MultiSelect, which allows to set the box to accept one or multiple values (by default accepts a single value only). Furthermore, the ListStyle property allows to show values as options (when set to 1-fmListStyleOption). Combining multiple file selection along option style displays a list box with checks for each value (see image below).

Another key property of the ListBox control (and for ComboBox) is the property List. It allows to add an array of values to the box. The code below is used to populate the list box with values in the picture above before the Userform opens.

 Private Sub UserForm_Initialize()
     ListBox1.List = Array("Label", "Frame", "TextBox", "ComboBox", _
     "ListBox", "CommandButton", "CheckBox", "OptionButton", _
     "SpinButton", "MultiPage", "Image")
 End Sub

Along with List, other useful properties of the ComboBox are the properties ListCount and ListIndex. ListCount returns the total number of items in the box while ListIndex returns the index of the selected item (starting at 0). This line of code saves the value or text of the selected item into a variable (MySelection).

 MySelection = ListBox1.List(ListBox1.ListIndex)

However, when allowing multiple selection, the ListIndex property only returns the index of the last selected item. In order to get each of the selected items we need to loop through the list and check if selected as indicated below.

 For i = 0 To ListBox1.ListCount - 1
     If ListBox1.Selected(i) = True Then
         r = r + 1
         Cells(r, 1).Value = ListBox1.List(i)
     End If
 Next i

That code could go in a macro (inside a standard module) being called from the Click event procedure of the button (Add to sheet). It could also be in the event procedure within the Userform module itself.

 

ListBox Methods

The ListBox control (and also ComboBox) have two methods to add and remove entries in the box: AddItem and RemoveItem.  The method AddItem requires the value or text of the item and an optional parameter that specifies the position in the list (zero-based). If not specified, the item is added at the end. The following example adds the project code (inside a text box) and the  RAG status (from a combo box) to the list box when the button is clicked.

 Private Sub CommandButton1_Click()
     projectID = TextBox1.Value
     RAGstatus = ComboBox1.Value
     ListBox1.AddItem projectID & ": " & RAGstatus
 End Sub

Since the index/position was not specified, each item is added at the end. Here’s how it looks after having added four items (four projects) to the list box.

 

On the other hand, the RemoveItem only has one parameter, which is the index or position of the item to be removed in the list (zero-based). The line of code below could go inside the Click event procedure of another button to delete a selected entry (the only one selected or the last one selected if multiple selection is allowed).

ListBox1.RemoveItem ListBox1.ListIndex

 

ListBox Events

The ListBox control has as many events as the ComboBox or TextBox. The most commonly used event of the ListBox is the Change event. We may want to take immediate action when an item in the list box is selected. The example below displays a message when selecting an item in the list box.

 Private Sub ListBox1_Change()
     Dim MySelection As String
     MySelection = ListBox1.List(ListBox1.ListIndex)
     MsgBox "Project selected: " & MySelection
 End Sub

This other example updates the number of items selected each time an element in the list box is selected or deselected. We have seen earlier the same loop used to verify which items are selected. Then, a numeric variable increments when selected and writes the value to cell A1 after the loop ends.

 Private Sub ListBox1_Change()
     Dim i As Integer, selected As Integer
     For i = 0 To ListBox1.ListCount - 1
         If ListBox1.selected(i) = True Then
             selected = selected + 1
         End If
     Next i
     Range("A1").Value = selected
 End Sub

The MouseDown event of the ListBox control is triggered before the Change event. That could be useful to perform some action before an item in the list is actually selected. If Change is not used, Click or DblClick events, and MouseDown or MouseUp may be used in some scenarios.

 

<<< Previous | Next >>>