Excel: VBA Macro Code for Multi Select Drop Down List

On May 11, 2018, in VBA, by Dipin Krishna

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.
Initial Drop Down List

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.

Initial Drop Down ListSelected Item 1Selected Item 2Selected Item 3Selected None

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
			Oldvalue = Target.Value
			If Newvalue = "None" Or Oldvalue = "None" Or Oldvalue = "" Then
				Target.Value = Newvalue
				Dim strArray() As String
				strArray = Split(Oldvalue, ";")
				If IsInArray(Newvalue, strArray) Then
					Target.Value = Oldvalue
					Target.Value = Oldvalue & ";" & Newvalue
				End If
			End If
		End If
	End If
	Application.EnableEvents = True
	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!

Tagged with:  

Leave a Reply