Create Data Validation Drop-Down List with Multiple Selection in Excel

In this article, we will learn how to create an excel data validation drop-down list with multiple selection. While we use data validation it only allows us to select one item from the list. But, what if we want to select multiple items from the drop-down menu of a data validation list. So, in this tutorial, we will use some macros to solve the limitation of multiple selections from a data validation drop-down list.


Download Practice Workbook

You can download the practice workbook from here.


3 Examples  to Create Data Validation Drop-Down List with Multiple Selection in Excel

The process of applying a VBA macro to create a data validation drop-down list with multiple selections is a bit of a complex process. So, to make you understand better we will demonstrate 3 different examples of multiple selections from a  data validation drop-down list.


1. Create Excel Data Validation Drop-Down List for Multiple Selection in a Single Cell

First and foremost, we have the following dataset, which consists of the names of 4 countries. We want to create a data validation drop-down list with multiple selections from these names. Normally, data validation will only allow us to select the name of one country in one cell. But, we want to input multiple country names from the data validation list in a single cell.

Create Excel Data Validation Drop-Down List for Multiple Selection in a Single Cell

So, let’s see the steps to perform this action.

STEPS:

  • Firstly, select cell range (D4:D8). Set the name range ‘dv_list_0’.

Create Excel Data Validation Drop-Down List for Multiple Selection in a Single Cell

  • Secondly, select the cell range (B5:B8) and set the name range ‘Country_Range’.

Create Excel Data Validation Drop-Down List for Multiple Selection in a Single Cell

  • Thirdly, go to Data > Data Tools > Data Validation > Data Validation.

Create Excel Data Validation Drop-Down List for Multiple Selection in a Single Cell

  • The above action will open a new dialogue box named ‘Data Validation’.
  • Next, select the option LIst from the Allow Enter the following formula in the Source text field:
=dv_list_0
  • Click on OK.

Create Excel Data Validation Drop-Down List for Multiple Selection in a Single Cell

  • So, a drop-down icon will appear on the right side of selected cells.

Create Excel Data Validation Drop-Down List for Multiple Selection in a Single Cell

  • Then, right-click on the sheet name of the active sheet. Select the option ‘View Code’.

  • The above command will open a blank VBA Insert the following code in that module:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Value_Old As String
Dim Value_New As String
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, ActiveSheet.Range("Country_Range")) Is Nothing Then
Application.EnableEvents = False
Value_New = Target.Value
On Error Resume Next
Application.Undo
On Error GoTo 0
Value_Old = Target.Value
If InStr(Value_Old, Value_New) Then
If InStr(Value_Old, ",") Then
If InStr(Value_Old, ", " & Value_New) Then
Target.Value = Replace(Value_Old, ", " & Value_New, "")
Else
Target.Value = Replace(Value_Old, Value_New & ", ", "")
End If
Else
Target.Value = ""
End If
Else
If Value_Old = "" Then
Target.Value = Value_New
Else
If Value_New = "" Then
Target.Value = ""
Else
If InStr(Target.Value, Value_New) = 0 Then
Target.Value = Value_Old & ", " & Value_New
End If
End If
End If
End If
Application.EnableEvents = True
Else
Exit Sub
End If
End Sub
  • Click on the Run button or press the F5 key to run the code.

  • Now, we will give the macro a name and click on Run. The name of the Macro for this example is VBA.

  • After that, select USA from the drop-down menu of cell B5. It will input the country name USA in cell B5.

  • Lastly, select Canada and Mexico from the drop-down also. We can see that all the values from the drop-down menu are selected in one cell.

Read More: How to Create Excel Drop Down List for Data Validation (8 Ways)


2. Insert Multiple Selection to Adjacent Columns by Creating Excel Data Validation Drop-Down List

In the second example, we will create a data validation drop-down list for multiple selections in adjacent columns. If we select any item from the data validation drop-down list it will get automatically selected in the adjacent column. We will continue with our previous dataset to illustrate this example.

Insert Multiple Selection to Adjacent Columns by Creating Excel Data Validation Drop-Down List

So, let’s take a look at the steps to solve this example.

STEPS:

  • First, select cell D5. Create a data validation drop-down like the previous method. Use the range (B5:B8) as source value for validation.

Insert Multiple Selection to Adjacent Columns by Creating Excel Data Validation Drop-Down List

  • Next, right-click on the active sheet name and select the option ‘View Code’.

Insert Multiple Selection to Adjacent Columns by Creating Excel Data Validation Drop-Down List

  • A new blank VBA module will Write down the following code in that blank module:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim DV_Range As Range
Dim Col_i As Integer
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set DV_Range = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If DV_Range Is Nothing Then GoTo exitHandler
If Intersect(Target, DV_Range) Is Nothing Then
Else
Application.EnableEvents = False
If Target.Column = 4 Then
If Target.Value = "" Then GoTo exitHandler
If Target.Validation.Value = True Then
Col_i = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
Cells(Target.Row, Col_i).Value = Target.Value
Else
MsgBox "Invalid entry"
Target.Activate
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
  • Press the F5 key or click on the Run icon to run the code.

Insert Multiple Selection to Adjacent Columns by Creating Excel Data Validation Drop-Down List

  • Then, a new dialogue box named Macros Create a macro named VBA1 and click on Run.

Insert Multiple Selection to Adjacent Columns by Creating Excel Data Validation Drop-Down List

  • After that, from the drop-down icon of cell D5 select the country named the USA.

  • The above command input the country name USA in cell E5 which is in the adjacent column E.

  • Finally, select Canada and Mexico We can see the selected values take place in the adjacent columns respectively.

Read More: Default Value in Data Validation List with Excel VBA (Macro and UserForm)


Similar Readings:


3. Make Multiple Selection in Separate Rows with Data Validation Drop-Down List in Excel

The third example is so much similar to the second one. In the second example, multiple selections happened in the adjacent columns whereas in this example multiple selections will take place in separate rows. For instance, to create a data validation drop-down list for multiple selections in this example we will also continue with the dataset that we used in the previous examples.

Make Multiple Selection in Separate Rows with Data Validation Drop-Down List in Excel

So, let’s go through the steps to perform this action.

STEPS:

  • In the beginning, choose cell D5. Like with the previous procedure, create a data validation drop-down. Validate using the range (B5:B8) as a source value.

Make Multiple Selection in Separate Rows with Data Validation Drop-Down List in Excel

  • Next, right-click on the active sheet. From the available options select ‘View Code’.

Make Multiple Selection in Separate Rows with Data Validation Drop-Down List in Excel

  • The above action opens a blank VBA Input the following code in that module:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim DV_Range As Range
Dim Row_1 As Long
Dim Col_1 As Long
Col_1 = Target.Column
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set DV_Range = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If DV_Range Is Nothing Then GoTo exitHandler
If Intersect(Target, DV_Range) Is Nothing Then
Else
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 4
If Target.Offset(0, 1).Value = "" Then
Row_1 = Target.Row
Else
Row_1 = Cells(Rows.Count, Col_1 + 1).End(xlUp).Row + 1
End If
Cells(Row_1, Col_1 + 1).Value = Target.Value
Target.ClearContents
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub
  • To run the code click on the Run icon or press the F5 key.

Make Multiple Selection in Separate Rows with Data Validation Drop-Down List in Excel

  • Then, we can see a new dialogue box named Macros. Give the macro name VBA2 and click on Run.

Make Multiple Selection in Separate Rows with Data Validation Drop-Down List in Excel

  • After that, from the drop-down list of cell D5 select the option USA.

  • So, we can see the name USA takes place in the same row but in the adjacent column.

  • In the end, select Canada, Mexico, and England one by one. We will see that the selected values take place one by one in the same column but in separate rows.

Read More: Excel Data Validation Drop Down List with Filter (2 Examples)


Conclusion

Therefore, this tutorial gives you an overview of creating a data validation drop-down list for multiple selections. To put your skills to the test, download the practice workbook that comes with this article. Please leave a comment in the box below if you have any questions. Our team will try to reply to you as soon as possible. In the future, keep an eye out for more innovative Microsoft Excel solutions.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo