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
To make data entry easy, you can follow these steps:
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.
On another worksheet in that Excel file, there are two named lists:
Those lists are used to set up the data validation drop down lists on the data entry sheet.
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:
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.
Here are a couple of ways that you can get multiple items into a cell that has a drop-down list.
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.
Another way to select multiple items for a cell, is with VBA code that runs when you select a drop down cell.
The short Excel video below shows this in-cell method, and there are detailed instructions on this page: Data Validation Select Multiple Items
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:
All the selected items are added to the active cell, separated by a comma and space character.
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:
The Visual Basic Editor opens, and you should see 3 windows
In the screen shot below:
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:
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:
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:
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!
Note: The RowSource property will be set by the VBA code, after you select a cell with a data validation drop down list.
In addition to the controls on the UserForm (ListBox, 2 buttons), there is also VBA code.
To see the UserForm's VBA code:
There are 3 procedures on the code module, separated by thin black lines
See the details for these 3 procedures, in the sections below.
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,
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"
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
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
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:
Me.lstDV.RowSource = strDVList
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
Last updated: May 17, 2022 7:55 PM