excel multi-select listbox output

Show a listbox, when a cell with an Excel drop down list is selected. Select multiple items from the list, and all selected items are entered in the active cell. There is also a sample file for single item selection.

Select Multiple Items From Drop Down List

To make data entry easier, you can add drop down lists to your worksheets. Then, click the arrow, and select an item to fill the cell.

Instead of allowing only one choice from the drop down, you can use a bit of programming, and allow multiple selections.

Choose Items from Listbox

To make it even easier to select multiple items, the sample file from this tutorial uses a listbox. When you click on a cell that has a drop down list, the listbox pops up, and shows all the choices.

Add a check mark to one or more of the items, then click OK. All the selected items are added to the cell, separated by a comma and space character.

Macro to Select Multiple Items

The sample workbook uses VBA code, which is set up to run automatically, when you click a cell on the worksheet.

• To see the code for the DataEntry sheet, right-click the sheet tab, and click on View Code.

On the worksheet's code module, you can see the code that runs when you select a different cell. The code checks to see if the cell has data validation list, and then gets the name of the list.

NOTE: This technique does not work for delimited lists that are entered directly into the data validation dialog box. It only loads named ranges, e.g. "MonthList", into the listbox.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String Dim strList As String On Error Resume Next Application.EnableEvents = False Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Not Intersect(Target, rngDV) Is Nothing Then If Target.Validation.Type = 3 Then strList = Target.Validation.Formula1 strList = Right(strList, Len(strList) - 1) strDVList = strList frmDVList.Show End If End If exitHandler: Application.EnableEvents = True End Sub

UserForm With Listbox

The last step in the procedure shown above is to show a UserForm named frmDVList.

That form is already in the sample file, and it has a listbox, and two buttons -- OK and Close.

NOTE: Click this link to see how to create a UserForm with a ListBox

When the form opens, the Initialize code sets the source for the listbox, based on the data validation list in the active cell

Private Sub UserForm_Initialize() Me.lstDV.RowSource = strDVList End Sub

There is also VBA code on the OK button, to get all the selected items, and copy them to the active cell. It then unloads the UserForm, so the listbox is hidden again.

Private Sub cmdOK_Click() Dim strSelItems As String Dim lCountList As Long Dim strSep As String Dim strAdd As String Dim bDup As Boolean On Error Resume Next strSep = ", " With Me.lstDV For lCountList = 0 To .ListCount - 1 If .Selected(lCountList) Then strAdd = .List(lCountList) Else strAdd = "" End If If strSelItems = "" Then strSelItems = strAdd Else If strAdd <> "" Then strSelItems = strSelItems & strSep & strAdd End If End If Next lCountList End With With ActiveCell If .Value <> "" Then .Value = ActiveCell.Value & strSep & strSelItems Else .Value = strSelItems End If End With Unload Me End Sub

Get the Sample Files

1. Multi Select: To try this technique, download the zipped sample file: Select Multiple Items from Listbox. The zipped file is in xlsm format, so enable macros when you open the workbook.
2. Single Select: There is also a single selection version of the file, that lets you pick one item to enter in the active cell. The zipped file is in xlsm format, so enable macros when you open the workbook.
3. Combo Multi: This sample file allows multiple selections, and also has a combo box above the list. Start typing in the combo box, and it will autocomplete. Press Enter, to add the combo box item to the list selections. Then, click OK, to add all the selected items to the cell.

More Tutorials

Data Validation Basics

List Box, Excel VBA

Data Validation - Create Dependent Lists

Data Validation Criteria Examples

Data Validation Tips

Data Validation Combo Box

Last updated: July 9, 2021 7:17 PM

• Hope someone can help.I have a listbox which is populated by product names, I know how to make multiple selections but what i dont know is the output from a multiple selection.For example when there is just one selection, it can be attached to a variable and that variable used to populate a cell, for instance.

  But if i select multiple options how would get them to become variables that i can manipulate, for example populate cells with them?

I hope i have been clear enough and make sense.

Look forward to your responses.

Re: Multiple Selection Listbox Output

Thank you very much for your reply, that is exactly what i needed.

Newly created posts will remain inaccessible for others until approved by a moderator.

The last reply was more than 180 days ago, this thread is most likely obsolete. It is recommended to create a new thread instead.