Select Multiple Items from Popup Listbox

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.

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

Download the Sample Data Validation File

To experiment with this technique, you can download the zipped sample file: Select Multiple Items from Listbox. The file is in xlsm format, so enable macros when you open the file.

There is also a single selection version of the file, that lets you pick one item to enter in the active cell.

Buy the Premium Version

There is a premium version of this technique, and you can see the details here: Data Validation Multi Select Premium

In the premium version, the list box automatically selects any items that are already in the cell. It also has buttons to Clear all the selections, and select all the items.

The premium version works with dependent lists too, and runs on a protected worksheet. There is also an option of showing a multi-select listbox, or a single-select version (this is helpful when working with dependent lists -- you don't want multiple items selected in the main columns).

The kit has 3 sample files, and a user guide, with details and screen shots, on how to add this technique to your own workbooks.

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