In this tutorial we learn how to create a drop-down list in Excel that lets you select multiple items. When you create a drop-down list using Excel data validation, you can only make one selection. With a few adjustments to the VBA code excel allow multiple selections.
Create the Drop-down List Using Data Validation
- In first of all create a list of values that you want to appear in the drop-down list on a separate worksheet.
2. Create dropdown list using Excel data validation- Go to Data –> Data Tools –> Data Validation.
3. In the Data Validation dialogue box, within the settings tab, select ‘List’ as Validation Criteria and for value selection click on Source box. After selection of value click on OK Button.
4. A dropdown list created in column B2.
VBA Code to allow Multiple Selections in a Drop-down List
To add the VBA (visual basic for applications) code to your drop-down list follow these steps:
- Go to the Developer Tab and click on Visual Basic or use the keyboard shortcut ALT F11 to open the Visual Basic Editor (VBE)
- Make sure the Project Explorer is visible – to open it use the keyboard shortcut CTRL R .
- In the Project Explorer select the worksheet that contains your drop-down list.
- In the Code window, paste the code below
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("B2")) Is Nothing Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Finally we create a multi value select dropdown list in excel with comma separate.
hello!,I really like your writing so much! share we be in contact more approximately your post on AOL? I require a specialist in this space to resolve my problem. Maybe that’s you! Looking ahead to peer you.