Excel VBA- ListBox

Objectives

• To provide detailed information about ListBox
• Types of ListBox
• Using ListBox in VBA applications

Please read the post till end to learn ListBox in depth. Images are used wherever required to make the discussion expilicit. Unnecessary verbosity is avoided, pictures are enough to understand the context.

Definition

A ListBox is a userform control which shows a list of data items. The list can be one column data items or it can be a list of multi-column data items.

Some facts about ListBox:

• A single column list can be considered as one-dimensional array and multi-column list as two-dimensional array. (List and Array are not same data structures).
• A ListBox allows users to select one or more rows of data in contrast to ComboBox which allows selection of single row from the drop-down rows. 
• The items in a list box can appear as plain text or they can appear with option buttons or check boxes in front of each item. 
• In case of option button, the user can select only one item from the list of the list box. 
• In case of check boxed items, the user can select more than one items.

Now we look at different types of ListBox.

Types of ListBox

1. Plain Style List Box with single row selection feature: 
2. Plain Style List Box with Multiple Selection Feature
3. Single-column ListBox
4. Multi-column ListBox

Plain Style List Box with single row selection feature: The below screenshot with its code allows user to select only one row at a time. So, in this case, data selection is basically from mutually exclusive items.

Private Sub UserForm_Initialize()
Dim vData As Variant
    With Me.ListBox1
        vData = Sheet1.Range("A2:A" & Sheet1.Range("A1").CurrentRegion.Rows.Count - 1)
        .List = vData
        .ListStyle = fmListStylePlain
        .ColumnHeads = False
        .MultiSelect = fmMultiSelectSingle          ‘’ select a single row or data item
    End With
End Sub

Plain Style List Box with Multiple Selection Feature

Look at the code carefully to see how properties of ListBox object are assigened in this case.

Private Sub UserForm_Initialize()
    Dim vData As Variant
    With Me.ListBox1
        vData = Sheet1.Range("A2:A" & Sheet1.Range("A1").CurrentRegion.Rows.Count - 1)
        .List = vData
        .ListStyle = fmListStylePlain
        .ColumnHeads = False
        .MultiSelect = fmMultiSelectMulti           ‘’ select multiple rows of the list
    End With
End Sub

Single-column ListBox

Look at the code carefully to see how properties of ListBox object are assigened in this case.

Private Sub UserForm_Initialize()
    With Me.ListBox1
        vData = Sheet1.Range("A2:A" & Sheet1.Range("A1").CurrentRegion.Rows.Count - 1)
        .List = vData
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectSingle
    End With
End Sub

Multi-column ListBox

Look at the code carefully to see how properties of ListBox object are assigened in this case.

Private Sub UserForm_Initialize()
Dim vData As Variant
    With Me.ListBox1
        vData = Sheet1.Range("A2:A" & Sheet1.Range("A1").CurrentRegion.Rows.Count - 1)
        .List = vData
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
    End With
End Sub

List all selected Items of the list box

Look at the code carefully to see how properties of ListBox object are assigened in this case.

Private Sub CommandButton1_Click()
'' List all selected items
    Dim x As Integer
    Dim Str As String
    Str = vbNullString
    For x = 0 To ListBox1.ListCount - 1
    '' Selected property is valid for ListBox and not for ComboBox
        If ListBox1.Selected(x) Then
            Str = Str & ", " & ListBox1.List(x)
        End If
    Next
    MsgBox Right(Str, Len(Str) - 1)
End Sub

Column Count Property
ColumnWidths Property

ColumnCount =0 it is used to suppress all the columns of the ListBox.

ColumnCount =-1 is used to display all the columns of the ListBox

The number of columns is a ListBox is called column count and each column of the ListBox can be given a definite width by the developer by the help of column Widths property. For example, if the column Count is 3 then ColumnWidths can be 55;85;74. In this example the width of 1^st column is 55, of 2^nd column is 85 and of 3^rd column is 74. The column widths of the ListBox cannot be more than its number of column count. Although, we can specify the column widths for less than the column count, If we specify the column widths for 4^th column while the column Count is 3 we get type mismatch error as depicted below:

Column widths must be written as double-quoted string and the widths be separated by semi-column. If it is colon by spelling error, run time error will be.

AddItem Method of ListBox

The AddItem method of list box is used to programmatically add items in the list at run time. So, it provides dynamic approach to add items in the list.

The AddItem method cannot be used if the RowSource property of the list box is not ZLS. So, whenever you want to use AddItem method, set the RowSource=”” to avoid error.  In other words, the listbox control must be unbound control to use AddItem method.  AddItem method has 2 parameters called item and index. The item is the value of item which is to be added to the list and index is the position in the list. If the index parameter is omitted, the item is added to the next row.

Let's consider the following procedures for the buttons shown in the below picture:

Add Items Button Procedure

Look at the code carefully to see how properties of ListBox object are assigened in this case.

Private Sub CommandButton1_Click()
    With ListBox1
        . RowSource = ""
        . AddItem "JAN"
        .AddItem "FEB"
        .AddItem "MAR"
        .AddItem "APR"
        .AddItem "MAY"
        .AddItem "JUN"
        .AddItem "JUL"
        .AddItem "AUG"
        .AddItem "SEP"
    End With
End Sub

Look at the code carefully to see how properties of ListBox object are assigened in this case.

Remove List Button Procedure

Private Sub CommandButton2_Click()
    If ListBox1.ListCount > 0 Then
        ListBox1.RemoveItem 0
    Else
        MsgBox "No item found in the listbox", vbOKOnly + vbInformation, "List Items"
    End If
End Sub

Select & Remove Button Procedure

Look at the code carefully to see how properties of ListBox object are assigened in this case.

Private Sub CommandButton3_Click()
    ''ListIndex returns the index of selected item in list box
    ListBox1.RemoveItem ListBox1.ListIndex
End Sub

ListBox List Style ( fmListStylePlain )

ListBox List Style ( fmListStyleOption )

When ListBox1.ListStyle = fmListStyleOption, we get the option buttons in the ListBox provided we don’t use the MultiSelect property of the ListBox with value not equal to fmMultiSelectSingle.

ListBox with check boxes:

ListBox1.ListStyle = fmListStyleOption and

ListBox1.MultiSelect = fmMultiSelectMulti

Text Vs Value Property of ListBox

Look at the code carefully to see how properties of ListBox object are assigened in this case.

Private Sub CommandButton1_Click()
    ''Text property refers to the Text of the TextColumn ListBox
    MsgBox ListBox1.Text, vbOKOnly + vbInformation, "Text Property"
    ''Value property refers to the Value of the BoundColumn ListBox
    MsgBox ListBox1.Value, vbOKOnly + vbInformation, "Value Property"
End Sub
Private Sub UserForm_Initialize()
    ListBox1.RowSource = Sheet1.Range("A2:C" & Sheet1.Range("A1").CurrentRegion.Rows.Count).Address
    '' Control Source cell stores the value as per BoundColumn
    ListBox1.ControlSource = Sheet1.Range("E1")
    ListBox1.ColumnCount = 3
    ListBox1.BoundColumn = 2
    ListBox1.TextColumn = 1 '' 3 will be for date of birth
End Sub

Selected Property

If list box has multiple selection enabled, you cannot use click event to show any message etc. The reason is very simple and intuitive. The VBA doesn't know for which one list item, it should raise the event. Therefore, don't use any such kind of coding if your list box has multiple selection allowed.

List box has its unique property called Selected. This property is not present for ComboBox control. This property returns a boolean value. You can pass list index as argument to Selected property.
For example, ListBox1.Selected(0) refers to the boolean state of first row selection. If the first row is selected, it will return true else it'll return false.

List Property

List box List property is a read write property which  can be used to read all items of the list. We can directly pass a complete list to the list box using list property. The data passed should be an array. The array can be one or two dimensional.

To pass an individual data item to the list, use row and column arguments of the List.

List box.List(rowindex, column index)

The rowindex and column index starts from zero.

Column Property


ListCount Property

ListCount property returns the total number of rows present in the ListBox.

ColumnCount property returns the total number of columns present in the ListBox.

The default value of ColumnCount is 1.

BoundColumn is an important concept with respect to ListBox and ComboBox.

The values of BoundColumn can be -1 to any positive integer.

ListBox Events

List box has a number of events such as change, click, afterupdate etc.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/examples-microsoft-forms