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.
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.
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.
The sample workbook uses VBA code, which is set up to run automatically, when you click a cell on the worksheet.
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
The last step in the procedure shown above is to show a UserForm named frmDVList. That form has a listbox, and two buttons -- OK and Close.
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
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.
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.
Last updated: October 24, 2016 3:26 PM