How do I select multiple options in a drop-down list?
How to make multiple selections in a dropdown list in Excel Ilker 8 months ago Excel doesnt have any built-in feature that allows you select multiple items in a dropdown. In this guide, we're goi…
How to make multiple selections in a dropdown list in Excel
Excel doesnt have any built-in feature that allows you select multiple items in a dropdown. In this guide, we're going to show you how to make multiple selections in a dropdown list in Excel.
Download Workbook
Preparation
- Start by creating a standard data validation dropdown. The rest will be handled with a VBA macro.
- Once the dropdown is ready, press the Alt + F11 keys to open VBA
- Double-click on the sheet item on the Project pane to the left. This will open the corresponding editor on the right.
The code should be in the worksheets editor containing the dropdown, because the code tracks changes in the cell.
VBA Code to make multiple selections in a dropdown list
All you need to do is to copy and paste the following code into your file and change the cell reference of the dropdown.
Private Sub Worksheet_Change(ByVal Target As Range)
'Define variables
Dim ExistingValue As String
Dim NewValue As String
Dim Separator As String
'If an error occurs, enable events and quit the code
On Error GoTo Quit
'Check if the dropdown cell is changed
If Target.Address = "$F$2" Then
'If user deletes the dropdown cell's data do nothing
If Target.Value = "" Then GoTo Quit
'The fun begins
Application.EnableEvents = False
NewValue = Target.Value
Application.Undo
ExistingValue = Target.Value
If ExistingValue = "" Then
Target.Value = NewValue
Else
'Check if the dropdown item is already selected
If InStr(1, ExistingValue, NewValue) = 0 Then
Target.Value = ExistingValue & ", " & NewValue
Else
Target.Value = ExistingValue
End If
End If
End If
Quit:
Application.EnableEvents = True
End Sub
The dropdown cells address is on the 9th row of the code. If you are using a named range, replace the cell address $F$4 with Range(<named range>).Address. To learn more about the cell referencing in VBA, check out How to refer a range or a cell in Excel VBA.
Check the 22nd line if you want to use another character as a separator and replace the comma with any character you want.
Parsing multiple selected values
You can even parse the selected values to consolidate the corresponding data. Check out following example.
You can find detail information about text parsing in our How to split text with formulas in Excel article.
Video liên quan
How do I select multiple options in a drop-down list?
Related post
Home
Nhà thiết kế WebTôi là admin trang go plus là một người có đam mê với Blogspot, kinh nghiệm 5 năm thiết kế ra hàng trăm mẫu Template blogpsot như" Bán hàng, bất động sản, landing page, tin tức...