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:
- The selected call fragment is inserted - two versions are discussed above
- The implementation modules modSelectionBox and FormSelectionBox are added to the VBAProject.
- A reference is set to the Microsoft Forms 2.0 Object Library