SelectionBox dialog for simple selection in VBA macros

The easiest and most common way to interact with users when making VBA macros is by using InputBox and MessageBox. This article inroduces a third dialog, the SelectionBox. The SelectionBox dialog contains a listbox and Ok and Cancel buttons. It can be called in two ways allowing the user to either select only one item from the list, or multiple. By specifying the Prompt text it is indicated that selection of a value is mandatory.

SelectionBoxSingle to allow the user to select one value only

The code below shows how to open the SelectionBox dialog with the listbox filled with sample values allowing the user to select one value only, and print the selected value - if any.

Dim varArrayList As Variant
Dim strSelected As String
varArrayList = Array("value1", "value2", "value3")
strSelected = SelectionBoxSingle(List:=varArrayList)
If Len(strSelected) > 0 Then
    Debug.Print strSelected
End If

SelectionBoxMulti to allow the user to select multiple values

The code below shows how to open the SelectionBox dialog with the listbox filled with sample values allowing the user to select multiple values. The first of the selected values is printed - if any. The use of argument Prompt:="Select one or more values" is programmed in the SelectionBox UserForm to result in the Prompt message if the user has not selected any values.

Dim varArrayList As Variant
Dim varArraySelected As Variant
varArrayList = Array("value1", "value2", "value3")
varArraySelected = SelectionBoxMulti(List:=varArrayList, Prompt:="Select one or more values", _
                                    SelectionType:=fmMultiSelectMulti, Title:="Select multiple")
If Not IsEmpty(varArraySelected) Then 'not cancelled
    Debug.Print varArraySelected(0)
End If

Inserting SelectionBox using Code VBA

The SelectionBox modules are included in Code VBA - download free trial here. Alternatively, you can just pick up a demo xls project When you select one of the four ways to call the SelectionBox included in the menu, see image below:

1. The selected call fragment is inserted - two versions are discussed above
2. The implementation modules modSelectionBox and FormSelectionBox are added to the VBAProject.
3. A reference is set to the Microsoft Forms 2.0 Object Library