The classic drop-down list in an Excel sheet is great, but it only allows you to select one option from the presented set. Sometimes this is exactly what you want, but there are situations where the user needs to be able to choose some elements from the list.
Let’s look at several typical implementations of such a multi-select list.
Option 1. Horizontal
The user selects items from the drop-down list one by one, and they appear to the right of the cell being changed, automatically being listed horizontally:
Drop-down lists in cells C2:C5 in this example are created in a standard way, i.e.
- select cells C2:C5
- tab or menu Data choose a team Data Validation
- in the window that opens, select an option List and specify as a range Source cells with source data for list A1:A8
Then you need to add a macro to the sheet module, which will do all the main work, i.e. add selected values to the right of the green cells. To do this, right-click on the sheet tab with drop-down lists and select the command Source code. Paste the following code into the Visual Basic editor window that opens:
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End(xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub If necessary, replace the sensitive range of the drop-down lists C2:C5 in the second line of this code with your own.
Option 2. Vertical
The same as in the previous version, but the new selected values are not added to the right, but to the bottom:
It is done in exactly the same way, but the handler macro code changes slightly:
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub Again, if necessary, replace the sensitive range of the C2:F2 drop-down lists with your own in the second line of this code.
Option 3. With accumulation in the same cell
In this option, the accumulation occurs in the same cell where the drop-down list is located. The selected elements are separated by any given character (for example, a comma):
Drop-down lists in green cells are created in a completely standard way, as in the previous methods. All the work is done, again, by a macro in the sheet module:
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval) <> 0 And oldval <> newVal Then Target = Target & "," & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub If desired, you can replace the separator character (comma) in the 9th line of code with your own (for example, a space or a semicolon).
- How to create a simple drop down list in an excel sheet cell
- Dropdown list with content
- Dropdown list with missing options added
- What are macros, how to use them, where to insert macro code in Visual Basic