Select Multiple Items from Popup Listbox

Show a pop-up 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.

Author: Debra Dalgleish

Select Items From Drop Down List

To make data entry easy, you can follow these steps:

• Add drop down lists to your worksheets, using the Excel data validation feature
• Then, select a cell, and click its drop-down arrow
• Select an item from the drop down list
• The selected item is automatically entered in the cell

For example, in the screen shot below, there is an Excel table, with drop down lists for Day and Month.

Select "Tuesday" from the drop down list, to add it to the active cell.

Worksheet Lists for Cell Drop Downs

On another worksheet in that Excel file, there are two named lists:

• DayList - list of weekdays, in cells A1:A7
• MonthList - list of months, in cells C1:C12

Those lists are used to set up the data validation drop down lists on the data entry sheet.

Named Ranges Required

The multiple selection techniques dexcribed below will only work with drop down lists that are based on a named range.

The macros will not work with data validation drop down lists that are based on the following:

• worksheet cell reference, such as this range of cells: =Admin_Lists!$A$1:$A$7
• delimited list entered directly into data validation dialog box, such as: Jan,Feb,Mar

Select Multiple Items from Drop Down

On an Excel worksheet, you can only select one item from a data validation drop down list of items. Later, if you choose a different item from the drop down, the new item replaces the previous selection in the cell.

However, with a bit of Excel programming, you select multiple items, and keep all of them in the cell.

Two Ways to Select Multiple Items

Here are a couple of ways that you can get multiple items into a cell that has a drop-down list.

1) Listbox

One way to select multiple items is with a pop-up Listbox. The instructions for this method are on this page, in the sections below the video.

• Select multiple items at once, from the listbox
• All selections are added to the cell, without removing previous selections from the cell

2) In-cell

Another way to select multiple items for a cell, is with VBA code that runs when you select a drop down cell.

• Select items one at a time, from the drop down list
• Each new selection is added to the cell, without removing previous items

The short Excel video below shows this in-cell method, and there are detailed instructions on this page: Data Validation Select Multiple Items

Choose Multiple Items from Listbox

To make it easy to select multiple items for a cell, all at once, set up a pop-up listbox in your workbook.

The setup steps are described below, and you can get the completed workbook in the Download section, at the end of this page..

Here's how the completed pop-up listbox works:

• On the worksheet, click on a cell that has a drop down list
• The VBA listbox pops up automatically, and shows all the choices from the cell's drop down list.
• Add a check mark to one or more of the items in the list box
• When you're finished selecting items, click the OK button.

All the selected items are added to the active cell, separated by a comma and space character.

Macro and UserForm

The sample workbook, that you can get in the Download section, uses VBA macros.One macro is an Event procedure, and it runs automatically, when you click a cell (that's an event) on the worksheet.

There is also an Excel UserForm in the workbook, with a listbox and two buttons

To see the code and the UserForm, follow these steps:

• Right-click the sheet tab for the DataEntry sheet
• In the pop-up menu, click on View Code.

Visual Basic Editor

The Visual Basic Editor opens, and you should see 3 windows

• Project Explorer - at the top left - shows all open workbooks, and the objects in them
• Properties - at the bottom left - details about the selected object
• Code - at the top right - code for the selected object

In the screen shot below:

• DataEntry sheet is the selected object, and its code is showing in the Code window.
  • The code is shown and explained in a later section, below
• In the Project Explorer, I've highlighted the UserForm, which is named frmDVList.
  • If necessary, click the plus sign to the left of a folder icon, to see the objects inside it

UserForm With Listbox

In the screen shot above, you can see the UserForm object, in the Project Explorer.

NOTE: To build your own UserForm and ListBox, see instructions here: Create UserForm with ListBox

To see the UserForm in design mode:

• Double-click on the UserForm in the Project Explorer window.
• The UserForm appears in the Object window, , where the Code window was, previously.

See Object Properties

When an object is selected in the Project Explorer, or in the Object window, you can see that object's details in the Properties window.

In the screen shot below:

• UserForm is selected in the Object window.
  • It has a thick grey outline, with a square black handle at its bottom left corner.
• At the top of the Properties window, you can see the name that I gave to the UserForm - frmDVList
  • I used that name because it will show a pop-up data validation list.
• You can slso see the form's name at the top of the list of properties: (Name) - frmDVList
  • At any time, you can change the form's name, or any of the other properties in the list
• In the Caption property, you can see the text that appears at the top of the UserForm - Select Items to Add

ListBox Properties

On the UserForm, there are two command buttons, and a Listbox.

To see the ListBox properties, click the ListBox in the Object window

In the screen shot below:

• ListBox is selected in the Object window.
  • It has a thick grey outline, and white handle at its bottom centre (not visible in screen shot)
• At the top of the Properties window, you can see the name that I gave to the ListBox - lstDV
  • I used that name because it will show items from the data validation list.

ListBox Property Settings

Here are some of the other ListBox property settings that I used, and you can see all the settings if you download the sample file.

Note: The MultiSelect property setting is most important!

• ColumnCount: 1
• ColumnHeads: False (do not show column headers in list)
• ListStyle: 1 - fmListStyleOption (this shows checkbox or radio buttons)
• MultiSelect: 1 - fmMultiSelectMulti
• SpecialEffect: 2 - fmSpecialEffectSunken

Note: The RowSource property will be set by the VBA code, after you select a cell with a data validation drop down list.

UserForm VBA Code

In addition to the controls on the UserForm (ListBox, 2 buttons), there is also VBA code.

To see the UserForm's VBA code:

• Right-click on the UserForm in the Project Explorer window.
• Click View Code

There are 3 procedures on the code module, separated by thin black lines

• cmdClose_Click - runs when you click the Close button (named cmdClose)
• cmdOK_Click - runs when you click the OK button (named cmdOK)
• UserForm_Initialize - automatically runs when the UserForm opens

See the details for these 3 procedures, in the sections below.

UserForm Initialize Code

When the form opens, the UserForm_Initialize code sets the RowSource property for the ListBox.

Here is the code, and there is a brief explanation below.

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

In that short procedure,

• Me represents the UserForm
• lstDV.RowSource is the ListBox RowSource property
• strDVList is a variable, that stores a value temporarily

The strDVList variable gets a list name based on the data validation cell that you clicked. For example, if you clicked a Month cell on the DataEntry sheet, the strDVList variable would be "MonthList"

Close Button VBA Code

When you click the Close button, the following code runs.

There is only one line in this procedure, and it unloads the UserForm, which means that you can't see it or interact with the UserForm any longer:

Private Sub Sub cmdClose_Click()
  Unload Me
End Sub

OK Button VBA Code

When you click the OK button, the following code runs.

This code gets all the selected items from the ListBox, and copies them to the active cell. At the end, the code unloads the UserForm, so the pop-up ListBox is hidden again.

Note: There are comments in the code, to describe what the macro does.

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 = ", "  'separator for items in cell

With Me.lstDV
  'go through all items in list
  '  numbering starts at zero
   For lCountList = 0 To .ListCount - 1
      'if item is selected, get item name
      '  strAdd variable is item name
      '     or empty string
      If .Selected(lCountList) Then
         strAdd = .List(lCountList)
      Else
         strAdd = ""
      End If
      
      'if no previous items,
      '  strSelItems =strAdd
      If strSelItems = "" Then
         strSelItems = strAdd
      Else
      'if prev items, add separator
      '  and latest item
         If strAdd <> "" Then
            strSelItems = strSelItems _
              & strSep & strAdd
         End If
      End If   
   Next lCountList
End With

With ActiveCell
  'if active cell is not empty, add separator
  '  and all items collected from ListBox
   If .Value <> "" Then
      .Value = ActiveCell.Value _
        & strSep & strSelItems
   Else
   'if active cell empty, and all items
   '  collected from ListBox
      .Value = strSelItems
   End If
End With
Unload Me
End Sub

Global Variable

There is one regular code module, named modSettings, in the sample workbook.

That module has one line of code, to define a global variable.

 Global strDVList As String

That variable is used in the UserForm, and in Worksheet VBA code, shown in the next section:

• Worksheet code gets data validation source list name from active cell
• That name is passed to the strDVList variable, for temporary storage
• Then, when UserForm opens, strDVList variable is used as the RowSource for the ListBox

Me.lstDV.RowSource = strDVList

DataEntry Sheet VBA Code

On the DataEntry sheet's code module, you can see the code that runs when you select a different cell.

First, the code checks to see if the cell has data validation list. If so, it gets the source name for the list. Then, it opens the UserForm.

Here is the code, with comments to describe what the macro does.

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
'temporarily turn off Events
Application.EnableEvents = False
'set a range with all DV cells on sheet
   Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
   On Error GoTo exitHandler
'if no DV cells, exit macro
   If rngDV Is Nothing Then GoTo exitHandler
   If Not Intersect(Target, rngDV) Is Nothing Then
  'if active cell IS in DV range
    'check if it's a List (DV type 3)
      If Target.Validation.Type = 3 Then
         'if list, get source list name
         strList = Target.Validation.Formula1
         strList = Right(strList, Len(strList) - 1)
         'pass source list name to global variable
         strDVList = strList
         'open UserForm
         frmDVList.Show
      End If
   End If

exitHandler:
'turn on Events
  Application.EnableEvents = True

End Sub

Download 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.
4. 2-Column Listbox: This sample file has a 2-column Listbox, and it allows multiple selections, and also has a combo box above the list. For the Day column, a short day name is also added to the worksheet. 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