Mastering ListBox Multi-Selection: Obtaining a Character String of Selected Items in VBA

Photo by AltumCode on Unsplash

Introduction

ListBox controls are a popular way to display lists of items in a user interface, allowing users to select one or more items from the list. In this article, we’ll discuss how to obtain a character string containing all the selected elements in a ListBox separated by a semicolon (;). This is a common question that arises in forums related to ListBox controls, and we will provide a detailed solution using VBA (Visual Basic for Applications) code.

We will begin by providing a brief reminder of the properties involved in ListBox controls, then we will explore the VBA code required to achieve the desired outcome. Next, we will discuss the controls used in the example, followed by the UserForm code and an explanation of how to invoke the function. Finally, we will provide a list of resources for further learning.

Properties of ListBox controls

To perform multi-selection in a ListBox control, the MultiSelect property must be set accordingly. The MultiSelect property can take one of the following three values:

1. fmMultiSelectSingle (0): Only one item can be selected (default).
2. fmMultiSelectMulti (1): Clicking or pressing the spacebar selects or deselects an item in the list.
3. fmMultiSelectExtended (2): Combination of Shift or Ctrl key with mouse click. Shift + Click extends the selection from the previously selected item to the current item, and Ctrl + Click selects or deselects an item.

It is important to note that when the MultiSelect property is set to fmMultiSelectMulti or fmMultiSelectExtended, the Selected property of the ListBox should be used to determine which items are selected. Additionally, the Value property of the control will always be Null.

Another property to consider is the LifeStyle property. In a ListBox, setting this property to fmListStyleOption (1) displays “Options” buttons or “Checkboxes” for a multiple-selection list.

Procedure code

The VBA code provided below will return a string containing all the selected items in a ListBox MultiSelected:

Function GetSelectedValues(oListBox As MSForms.ListBox) As String
  ' Returns a string containing all the items selected in a ListBox MultiSelected
  ' Argument
  'oListBox Affected ListBox control
  Dim Element As Integer, Count As Integer
  Sun tbl As Variant
  ' Load selected items into a table
  With oListBox
    For Elem = 0 To .ListCount - 1
      If .Selected(Elem) Then
         If Count Then ReDim Preserve tbl(Count) Else ReDim tbl(Count)
         tbl(Count) = .List(Elem)
         Count = Count + 1
      End If
    Next
  End With
  If IsArray(tbl) Then GetSelectedValues = Join(tbl, ";")
End Function

The controls used

The following controls are used in the example provided:

1. Name: UserForm, usf_MultiSelect
2. Name: ListBox, ListBox1
3. Name: CommandButton, cmdOk
4. Name: CommandButton, cmdCancel

UserForm code

The following VBA code is associated with the UserForm:

Option Explicit
Private Sub cmdCancel_Click()
  Me.Hide
End Sub
Private Sub cmdOk_Click()
  Me.Hide
End Sub

How to invoke the function

Use the following VBA code to invoke the function:

Sub Main_Select()
Const TableName As String = "t_Fruit" ' Table name used as list
Const LinkedCellName As String = "LinkedCell" ' Target cell name
Dim rng As Range
Dim msg As String

Set rng = Range(TableName)

With usf_MultiSelect
    With .ListBox1
        .ColumnWidths = mUserForm_Manager.GetColumnWidths(rng)
        .ColumnCount = rng.Columns.Count
        .List = rng.Value
        .MultiSelect = fmMultiSelectMulti ' Define the multiselection
        .ListStyle = fmListStyleOption ' Checkbox Style
    End With
    
    .Show
    
    msg = GetSelectedValues(.ListBox1)
    MsgBox IIf(Len(msg), msg, "No selection"), Title:="Selection result"
End With

Unload usf_MultiSelect
Set rng = Nothing
End Sub

Conclusion

In this article, we have provided a comprehensive solution to the question of how to obtain a character string containing all the selected elements in a ListBox separated by a semicolon. By leveraging the properties and functions of ListBox controls, we were able to create a VBA function that achieves this goal. Additionally, we discussed the controls used in the example, provided the UserForm code, and demonstrated how to invoke the function. The resources provided at the end of the article will be helpful for further learning and understanding the concepts discussed.