Excel: VBA Macro Code for Multi Select Drop Down List

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!