VBA macro user interaction with SelectionBox

Introduction

When writing VBA macros for MS Office interactions with the user are often limited to InputBox and MsgBox. The reason is they require very little effort to use compared to making a dedicated userform. The SelectionBox presented here is an attempt to extend the basic interaction vocabulary. 

The SelectionBox is implemented as a UserForm FormSelectionBox and a module modSelectionBox to be added to your VBA Project. The interface is similar to InputBox and has two variants: SelectionBoxSingle for returning a single value and SelectionBoxMulti for returning an array of selected values. Validation if the user has selected a value is activated by specifying the Prompt argument. 

You can import the SelectionBox modules into your Excel project from the SelectionBoxDemo file downloadable with this project. In addition you need to set a reference to Microsoft Forms 2.0 Object Library  (menu: Tools > References)

Download demo

Alternatively you can select the appropriate procedure call using the Code VBA add-in which then inserts both the code and modules and sets the MS Forms reference all in one go.

Using the code

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. In the SelectionBoxDemo.xls file you can see it in action by running the procedure SingleOpt in module modDemo.

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

The code below shows how to open the SelectionBox dialog with the listbox filled with sample values allowing the user to select multiple 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