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.

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

STEPS:

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

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

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

• 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:

• Click on OK.

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

• 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.

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.

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

• 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.

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

• 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

• Autocomplete Data Validation Drop Down List in Excel (2 Methods)
• How to Make a Data Validation List from Table in Excel (3 Methods)
• Apply Multiple Data Validation in One Cell in Excel (3 Examples)
• How to Remove Blanks from Data Validation List in Excel (5 Methods)
• Excel Data Validation Alphanumeric Only (Using Custom Formula)

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.

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.

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

• 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.

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

• 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

• How to Use IF Statement in Data Validation Formula in Excel (6 Ways)
• Use Data Validation in Excel with Color (4 Ways)
• How to Use Data Validation List from Another Sheet (6 Methods)
• Use Named Range for Data Validation List with VBA in Excel
• [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)