You have mastered creating drop downlists in excel (If you have do not worry we will link out blog post on how to do this at the bottom of this post)
But now you want to select multiple options from that list.
You may have already looked into this and seen it involved VBA code and thought nope too much for me.
A Boardroom Mum has got you it does involved VBA code but it is super simple!
Lets walk though how to create multiple selection options from a drop down list in excel.
Steps 1 & 2 fully broken down in post – How to create drop down lists in Excel.
Step 1 – Create your dropdown list
Write down a list.
Click Ctrl + T to make it a table.
Name the table
Step 2 – Create dropdown option list in a cell.
From the top ribbon
Click Data
Choose Data Validation
Under allow choose List.
Under source type =INDIRECT(“nameofyourtable”)
Click ok.
Step 3 – Enter VBA code for multiple selection options.
Right click the spreadsheet tab and choose view code
Copy the VBA code below and paste it into this worksheet.
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim DelimiterType As String
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
DelimiterType = “, “
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
If Intersect(Destination, rngDropdown) Is Nothing Then
‘do nothing
Else
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue = “” Then
‘do nothing
Else
If newValue = “” Then
‘do nothing
Else
Destination.Value = oldValue & DelimiterType & newValue
‘ add new value with delimiter
End If
End If
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Step 4 – Choose Multiple Options
In the cell that is linked to data validation you are now able to choose multiple options.
Bonus Format – Want to separate each item with a | no a ,?
Go back to the code – Right click the spreadsheet tab and click view code.
Where the code says DelimiterType = “, “
Change the , to a |
🎉
