How to Make Multiple Selections in a Drop-Down List in Excel - No Duplicates Allowed - VBA Code inc

Introduction

This tutorial will guide you through the process of creating a drop-down list in Excel that allows multiple selections without duplicates. This functionality is particularly useful for data entry tasks where users need to select several options from a predefined list. We will be using VBA (Visual Basic for Applications) to achieve this.

Step 1: Prepare Your Excel Workbook

• Open Excel and create a new workbook.
• Enter the list of items you want to include in your drop-down list in a single column (e.g., A1:A10).
• Select the cell where you want the drop-down list to appear (e.g., B1).

Step 2: Create the Drop-Down List

1. Go to the Data tab on the ribbon.
2. Click on Data Validation in the Data Tools group.
3. In the Data Validation dialog box:
   • Select List from the Allow dropdown.
   • In the Source field, input the range for your list (e.g., =A1:A10).
4. Click OK to create the drop-down list.

Step 3: Open the VBA Editor

• Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
• In the Project Explorer, find your workbook (usually named "VBAProject (YourWorkbookName)").
• Right-click on ThisWorkbook and select Insert > Module to create a new module.

Step 4: Insert the VBA Code

• Copy and paste the following VBA code into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim OldValue As String
    Dim NewValue As String
    Dim Separator As String
    Dim ItemList As Range

    Separator = ", " ' Specify the separator for multiple items
    Set ItemList = Me.Range("A1:A10") ' Adjust the range to match your list

    If Not Intersect(Target, Range("B1")) Is Nothing Then
        Application.EnableEvents = False
        On Error GoTo Exits

        If Target.Value = "" Then
            Exit Sub
        Else
            NewValue = Target.Value
            If Target.Value <> "" Then
                OldValue = Target.Value
                If InStr(1, OldValue, NewValue) = 0 Then
                    If OldValue = "" Then
                        Target.Value = NewValue
                    Else
                        Target.Value = OldValue & Separator & NewValue
                    End If
                Else
                    MsgBox "This item is already selected.", vbExclamation
                End If
            End If
        End If
Exits:
        Application.EnableEvents = True
    End If
End Sub

• Ensure that you adjust the ItemList range to match where your original list is located.

Step 5: Save and Enable Macros

• Save your workbook as a macro-enabled file (.xlsm).
• To enable macros, you might need to unblock the file. Right-click on the file, select Properties, and check the Unblock option if available.
• For further guidance on unblocking macros, refer to the Microsoft documentation here.

Step 6: Test Your Drop-Down List

• Return to your Excel worksheet and try selecting items from the drop-down list in cell B1.
• Ensure that selecting the same item again prompts a message and that your selections are concatenated in the cell.

Conclusion

You have successfully created a drop-down list in Excel that allows multiple selections without duplicates using VBA. This feature can significantly enhance data entry efficiency. Next steps could include customizing the VBA code further or applying this technique to other lists in your workbook. Happy Excel-ing!