How to Create a Dropdown List in Excel with Multiple Selections

After mastering the basics of creating a dropdown list in Excel, it’s time to take your skills a step further. In this tutorial, we delve into increasing the functionality of this process by creating a dropdown list in Excel with multiple selections possible. This advanced feature not only enhances your data entry experience but also brings an extra layer of functionality and accuracy to your spreadsheets.

Single Dropdown Lists in Excel

In a previous post, How to Create a Drop Down List in Excel, we focused on setting up a basic dropdown list in Excel. The example used was a Staff Allergy worksheet in which the Allergies could be assigned to the Staff Members using a Dropdown list, achieved via Data Validation.

Enabling Multiple Selections in your Dropdown List

Data Validation in Excel is effective for ensuring users enter valid data, such as specifying a single allergy. However, it poses a challenge when staff have multiple allergies, as Data Validation restricts us to just one selection from the dropdown list. Attempting to add another selection simply overwrites the original one.

To overcome this limitation and create a Dropdown List in Excel with Multiple Selections, the solution involves utilizing Excel’s VBA. This powerful feature extends Excel’s capabilities beyond its default functions. By crafting a Worksheet Change macro in VBA, we can dynamically adjust a cell’s content in response to user interactions, effectively enabling multiple selections within a single dropdown list. This approach not only solves the issue of single-choice limitations but also enhances the flexibility and functionality of Excel for more complex data management tasks.

Step-By-Step Guide to Implement Multiple Selections

• Start by opening up Excels VBA Editor. This can be done with shortcut command ALT + F11, or you can select on the Excel Ribbon by going to the Developer tab then Visual Basic.

Insert a New Module: In the VBA Editor, right-click on your workbook name in the “Project-VBAProject” pane and select Insert > Module.

Writing the Macro: Don’t worry about that, I’ll write it for you so just copy and paste the provided code into the Module then you can close the VBA Editor and save the workbook, remember to save it as a Macro-Enabled workbook (.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String

On Error GoTo ErrorHandler

‘ Define the range with data validation
Set rngDV = Me.Range(“C6:C11”) ‘Update this with the range where your multi-drop down list is applied

If Not Intersect(Target, rngDV) Is Nothing Then
Application.EnableEvents = False

For Each cell In Intersect(Target, rngDV)
newVal = cell.Value
Application.Undo
oldVal = cell.Value

‘ Check if the cell is being cleared
If newVal = “” Then
cell.Value = “”
Else
cell.Value = newVal
‘ Concatenate the new value if it is not already in the list
If InStr(1, oldVal, newVal) = 0 Then
If oldVal <> “” Then
cell.Value = oldVal & “, ” & newVal
End If
Else
‘ If new value is already in the list, toggle it off
If Len(oldVal) > Len(newVal) Then
cell.Value = Replace(oldVal, “, ” & newVal, “”)
Else
cell.Value = Replace(oldVal, newVal & “, “, “”)
End If
End If
End If
Next cell
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

Testing the Macro

This macro does the following:

• It first defines the range where the dropdown list is applied (C6:C11 in this example, but you should update it to your specific range).
• When a selection is made, the macro checks if the new value is already part of the list in the cell. If it isn’t, it appends the new value to the existing ones, separated by a comma.
• If the new value is already in the list, the macro removes it, effectively allowing a toggle functionality.

Testing the macro against the Staff Allergy example you can now select multiple options from the dropdown list in Excel and they appear in the cell, separated by a comma.

Understanding the Code

VBA can look complicated to those starting out on their Excel journey so let’s look into the code in more depth to understand what the main functions mean:

• Worksheet_Change(ByVal Target As Range): This is an event handler that triggers when a change is made in the worksheet.
• Dim statements: These are used to declare variables.
• If Not Intersect(Target, rngDV) Is Nothing: This line checks if the changed cell is within our defined dropdown list range.
• Application.EnableEvents = False: This prevents the macro from triggering itself.
• Application.Undo: This retrieves the old value before the change.
• InStr, Replace functions: These are string functions used to manipulate the cell values.

As you can see there are not that many unique functions used in this code and many of them are frequently used in VBA projects so they will become more familiar to you over time, not just for creating a dropdown list in Excel with multiple selections.

Limitations and Considerations

While the VBA method unlocks the functionality of multiple selections in a dropdown list, it’s crucial to be mindful of its limitations:

• Macro-Enabled Workbook: This method requires saving your file as a macro-enabled workbook (.xlsm), which might not be permissible in all work environments due to security protocols.
• Manual Error Checking: Unlike standard Data Validation, this approach lacks automatic error checking, necessitating careful data entry.
• Learning Curve: Familiarity with VBA is essential. Although it might seem daunting at first, gaining proficiency in VBA is a valuable skill for any Excel user aiming to handle more sophisticated tasks.

Summary

In summary, Excel’s default features have their limitations, but using VBA offers a robust way to enhance a Dropdown List in Excel with Multiple Selections. While this method requires a macro-enabled workbook and lacks built-in error checking, it significantly broadens Excel’s capabilities.

By mastering these VBA techniques, you can elevate your Excel skills, creating more versatile worksheets ideal for complex data management. This guide serves as a foundational step towards advanced Excel proficiency, ensuring your worksheets are both dynamic and user-friendly.

Keep Excelling,

Now you know everything about creating a Dropdown List in Excel with Multiple Selections its time to brush up on some VBA with these 5 Simple VBA Tips for Excel.