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.
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 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
Last updated: April 15, 2021 9:27 AM