This post assumes that you already have a data validation setup on all/few fields in a column which is now showing a drop-down for the fields in that column.
The following VBA Macro enables the user to select multiple items from the dropdown list.
The following are what the code does:
1. The items selected from the drop-down are separated by ‘;’.
2. Ignores the selection if the item has already been selected before.
3. You can clear the selection by setting a ‘None’ item in the drop-down. Please change the text as needed.
4. Enables multi-select drop-down for column 10, please add or change as needed.
Private Sub Worksheet_Change(ByVal Target As Range) Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = True On Error GoTo Exitsub If Target.Column = 10 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 Newvalue = "None" Or Oldvalue = "None" Or Oldvalue = "" Then Target.Value = Newvalue Else Dim strArray() As String strArray = Split(Oldvalue, ";") If IsInArray(Newvalue, strArray) Then Target.Value = Oldvalue Else Target.Value = Oldvalue & ";" & Newvalue End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0)) End Function
Hope this helps!