Can you create a drop down list in Excel with multiple selections?

Practice Workbook

You are welcome to download the practice workbook from the link below.

Table of Contents Show

Practice Workbook
Multiple Selection in a Drop-Down List
1. Select Multiple Items (Allows Duplicate Selection)
2. Select Multiple Items From Drop Down List (Unique Selection Only)
3. Select Items in Newline
Download Practice Workbook
Steps to Create Drop-Down List in Excel with Multiple Selections
Step 1: Creating a Drop-Down List by Data Validation
Step 2: Enabling the Drop-Down List to Accept Multiple Selection by VBA Code
Excel Data Validation - Select Multiple Items
Select Multiple Items from Popup Listbox
Preparation
How to create drop down list with multiple selections or values in Excel?
The Best Office Productivity Tools

Make Multiple Selection from Drop Down List.xlsm

Multiple Selection in a Drop-Down List

First of all, we need to create a drop-down list on the basis of our stationeries. Let’s create it quickly. Don’t hesitate to visit the article regarding the making of a drop-down list.

In the Data Validation dialog box select the LIST data type and insert the cell range of the items.

B4:B11 is the range that holds the stationery elements. Now you will find the drop-down list.

Read more: Excel Drop Down List Depending on Selection

1. Select Multiple Items (Allows Duplicate Selection)

A conventional drop-down list always selects a single item. Here you can see, we have selected Pen from the list (image below).

Now, if we select another item, let’s say Pencil

then it will replace the previous value. Only Pencil will remain selected.

To select multiple items, we need to use the VBA code. Open the Microsoft Visual Basic for Applications window (press ALT + F11 to open it).

Now double click on the worksheet name or number where you want to select multiple items within the drop-down list. You will find the code window for that particular sheet.

Here, is the code window for Sheet2 in our workbook (we have the drop-down list in this sheet).

Once the code window is opened, insert the following code there

Private Sub Worksheet_Change(ByVal Target As Range) Dim Oldvalue As String Dim Newvalue As String On Error GoTo Exitsub If Target.Address = "$D$4" 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 Oldvalue = "" Then Target.Value = Newvalue Else Target.Value = Oldvalue & ", " & Newvalue End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub

Save the code, and now try to select values in the drop-down list.

Having selected Pencil, we are going to select another item Notebook. And you can see, we found both the items (image below).

This code will allow us to repeat the selection. Let’s say if we select Pencil again,

we will find the item again in the selection box.

Code Explanation

We have declared two strings Oldvalue and Newvalue.

You can see we made the drop-down list in the D4 cell, that’s why our target address is D4. And in addition, we have rechecked whether the cell is using data validation or not using Target.SpecialCells.

Once a value is selected, we turned off events (Application.EnableEvents = False) so changes don’t trigger the event again. Then stored the selected item into the Newvalue.

After undoing the change, we have set the value into the Oldvalue. Then check whether the Oldvalue is empty or not. If empty (means only one value is selected), then return the Newvalue. Otherwise, concatenate the Oldvalue and Newvalue.

Before ending the reset the event, so that we can change if required.

Read more: Multiple Dependent Drop-Down List Excel VBA

2. Select Multiple Items From Drop Down List (Unique Selection Only)

In the earlier section, we have seen the multiple selections where repetition was allowed. If you don’t want that, then follow this section.

For convenience, we used a separate sheet for this demonstration. This time we are at Sheet3. Write the following code in the code window for this sheet.

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.Address = "$D$4" 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 Oldvalue = "" Then Target.Value = Newvalue Else If InStr(1, Oldvalue, Newvalue) = 0 Then Target.Value = Oldvalue & ", " & Newvalue Else: Target.Value = Oldvalue End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub

Is there any difference compared to the earlier code! Have a closer look, you will be able to spot the slight difference.

Here we have used a VBA function called INSTR. The INSTR function returns the position of the first occurrence of a substring in a string. Visit this INSTR article for further information.

Using this logical operation with InStr(1, Oldvalue, Newvalue) = 0, we have checked whether the values are found or not. If the logical operation returns TRUE (not found earlier) then it allows to select the item and concatenate with the earlier value.

Save the code and now try to select an item that has already been selected.

Here we have already selected Pencil, if we want to select that again, we can’t. It doesn’t allow duplicate values.

Read more: How to Create a Drop Down List From Another Sheet in Excel

3. Select Items in Newline

So far, we have found the items are separated by a comma. In this section, we will arrange the selected items in newlines.

For simplicity, we are merging a few cells with the D4 cell. To do that, select the cells you want to merge and click Merge & Center from the Alignment section of the Home tab.

The cell will gain more height.

Now, let’s look at the code for separating items through newline. Use the following code

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.Address = "$D$4" 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 Oldvalue = "" Then Target.Value = Newvalue Else If InStr(1, Oldvalue, Newvalue) = 0 Then Target.Value = Oldvalue & vbNewLine & Newvalue Else: Target.Value = Oldvalue End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub

The only difference from the previous code is that this time we used vbNewLine in between OldValue and NewValue.

vbNewLine provides a new line between the items.

Now select the items.

We select an item Pen that is showing in the image above. Now select another element.

You will find the two items are in different lines.

Here we have two values, that are in two different lines. Selecting another value will add that to another line. Every value will be in a new line.

Note that if you want another delimiter to separate the items, use that within double quotes in place of vbNewline.

Download Practice Workbook

How to Create Drop Down List in Excel with Multiple Selections.xlsm

Steps to Create Drop-Down List in Excel with Multiple Selections

Here we’ve got a data set with the Names of some books in a bookshop called Martin Bookstore.

Our objective today is to create a drop-down list based on this data set that takes multiple selections.

We will accomplish this in two steps.

Step 1: Creating a Drop-Down List by Data Validation

Step 2: Enabling that List to Accept Multiple Selections by VBA Code

Step 1: Creating a Drop-Down List by Data Validation

Select the cell where you want to enter the drop-down list. Then go to Data>Data Validation>Data Validation under the Data Tools section.

Click on Data Validation. You will get the Data validation dialogue box.

From the Allow option, select List. Then in the Source option, enter the range of the cells that you want to enter as the drop-down list ($B$4:$B$23 in this example).

Then click OK. You will find a drop-down list created in your selected cell.

Similar Readings:

How to Make a Drop Down List in Excel (Independent and Dependent)
Make Multiple Selection from Drop Down List in Excel (3 Ways)
How to Create Dependent Drop Down List in Excel
Create Drop Down List in Multiple Columns in Excel (3 Ways)

Step 2: Enabling the Drop-Down List to Accept Multiple Selection by VBA Code

Now we’ve created a drop-down list. But up till now, it accepts a single selection.

We want it to accept multiple selections.

To enable it to accept multiple selections, press Alt + F11 on your keyboard. It will open the Visual Basic Application (VBA) window.
In the left panel of the window, you will get a folder called VBA Project. Under this, there is another folder called Microsoft Excel Objects.

Double-click on the name of your worksheet (Sheet1 in this example). And it will open the VBA code window of the worksheet.

Now enter either of the two codes in this window, depending on whether you want to allow repetition in your multiple selections or not.

Case 1: VBA Code for Multiple Selection with Repetition

If you want multiple selections with repetition, enter the following code:

Code:

Note: Here in the 5th line of the code (If Target.Address = "$D$4" Then), “$D$4” is the cell where I am entering the list.

You use your one.

If you want to enter the list in multiple cells, then repeat the line for multiple cells using an or operator.

For example, if you want to enter the list in cells D4, F4, and H4, use:

If Target.Address = "$D$4" or Target.Address = "$F$4" or Target.Address = "$H$4" Then

Case 2: VBA Code for Multiple Selection without Repetition

And if you don’t want repetition, use this code instead.

Code:

Note: Here in the 6th line of the code (If Target.Address = "$D$4" Then), “$D$4” is the cell where I am entering the list.

You use your one.

If you want to enter the list in multiple cells, then repeat the line for multiple cells using an or operator.

For example, if you want to enter the list in cells D4, F4, and H4, use:

If Target.Address = "$D$4" or Target.Address = "$F$4" or Target.Address = "$H$4" Then

After entering any of the above codes, save the file as an Excel Macro-Enabled Workbook (*.xlsm).

Now come back to your worksheet and go to the drop-down list you created earlier.

You will be able to make multiple selections from the list now, separated by commas (,).

Excel Data Validation - Select Multiple Items

Instead of limiting a data validation drop down list to a single selection, you can use this macro to allow multiple selections. Video and written instructions, and get the free workbook to see how it works.

Multiple Item Selection Articles
Video Multiple Item Selection Examples
Select Items With Drop Down List
Select Multiple Items From Drop Down List
View the Code for Select Multiple Items
- Change the Column Number
- Change the Sheet and Range Names

Video: Multiple Item Selection Setup
Allow Editing With Multiple Selections
No Duplicates in Cell
Remove Previous Selections From Cell
Multiple Selection Sample Code
Code on Protected Worksheet
Download the Sample Data Validation File

Show a listbox, when a cell with an Excel drop down list is selected. Select multiple items from the list, and all selected items are entered in the active cell. There is also a sample file for single item selection.

Select Multiple Items From Drop Down List

Choose Items from Listbox

Macro to Select Multiple Items

UserForm With Listbox

Get the Sample Files

Preparation

Start by creating a standard data validation dropdown. The rest will be handled with a VBA macro.
Once the dropdown is ready, press the Alt + F11 keys to open VBA
Double-click on the sheet item on the Project pane to the left. This will open the corresponding editor on the right.

The code should be in the worksheet’s editor containing the dropdown, because the code tracks changes in the cell.

How to create drop down list with multiple selections or values in Excel?

By default, you can only select one item per time from a data validation drop-down list in Excel. How to make multiple selections from the drop-down list as below screenshot shown? The methods in this article can help you solve the problem.

Create drop down list with multiple selections with VBA code
Easily create drop down list with multiple selections with an amazing tool
More tutorial for drop down list...

Create drop down list with multiple selections with VBA code

You can apply the below VBA code to make multiple selections from the drop-down list in a worksheet in Excel. Please do as follows.

1. Open the worksheet you have set data validation drop-down list, right click on the sheet tab and select View Code from the context menu.

2. In the Microsoft Visual Basic for Applications window, copy the below VBA code into the code window. See screenshot:

VBA code: drop down list with multiple selections

Private Sub Worksheet_Change(ByVal Target As Range) 'Updated by Extendoffice 2019/11/13 Dim xRng As Range Dim xValue1 As String Dim xValue2 As String If Target.Count > 1 Then Exit Sub On Error Resume Next Set xRng = Cells.SpecialCells(xlCellTypeAllValidation) If xRng Is Nothing Then Exit Sub Application.EnableEvents = False If Not Application.Intersect(Target, xRng) Is Nothing Then xValue2 = Target.Value Application.Undo xValue1 = Target.Value Target.Value = xValue2 If xValue1 <> "" Then If xValue2 <> "" Then If xValue1 = xValue2 Or _ InStr(1, xValue1, ", " & xValue2) Or _ InStr(1, xValue1, xValue2 & ",") Then Target.Value = xValue1 Else Target.Value = xValue1 & ", " & xValue2 End If End If End If End If Application.EnableEvents = True End Sub

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

Now you can select multiple items from the drop-down list in current worksheet.

Notes:

1. Duplicate values do not allow in the drop-down list.
2. When closing the workbook, the VBA code will be removed automatically, and the multiple selection is unusable anymore. Please save the workbook as an Excel Macro-Enabled Workbook in order to keep the code working in the future.

Easily create drop down list with multiple selections with an amazing tool

Here highly recommended the Multi-select Drop-down List feature of Kutools for Excel for you. With this feature, you can easily select multiple items from the drop-down list in a specified range, current worksheet, current workbook or all opened workbooks as you need.

Before applying Kutools for Excel, please download and install it firstly.

1. Click Kutools > Drop-down List > Multi-select Drop-down List > Settings. See screenshot:

2. In the Multi-select Drop-down List Settings dialog box, please configure as follows.

2.1) Specify the applying scope in the Apply to section. In this case, I select Current worksheet from the Specified Scope drop-down list;
2.2) In the Text Direction section, select an text direction based on your needs;
2.3) In the Separator box, enter a delimiter which you will use to separate the multiple values;
2.4) Check the Do not add duplicates box in the Options section if you don’t want to make duplicates in drop-down list cells;
2.5) Click the OK button. See screenshot:

3. Please click Kutools > Drop-down List > Multi-select Drop-down List to enable the feature.

Now you can select multiple items from the drop-down list in current worksheet or any scope you have specified in step 2.

If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

Autocomplete when typing in Excel drop down list
If you have a data validation drop down list with large values, you need to scroll down in the list just for finding the proper one, or type the whole word into the list box directly. If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier. This tutorial provides the method to solve the problem.

Create drop down list from another workbook in Excel
It is quite easy to create a data validation drop down list among worksheets within a workbook. But if the list data you need for the data validation locates in another workbook, what would you do? In this tutorial, you will learn how to create a drop fown list from another workbook in Excel in details.

Create a searchable drop down list in Excel
For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values in the drop down list. For making drop down list searchable, try the method in this tutorial.

Auto populate other cells when selecting values in Excel drop down list
Let’s say you have created a drop down list based on the values in cell range B8:B14. When you selecting any value in the drop down list, you want the corresponding values in cell range C8:C14 be automatically populated in a selected cell. For solving the problem, the methods in this tutorial will do you a favor.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, chartsand anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

38 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

Say something here...

symbols left.

You are guest

or post as a guest, but your post won't be published automatically. Post

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
ofd · 1 months ago
Thank you so much !!!!
- Reply
To post as a guest, your comment is unpublished.
Margot · 8 months ago
Hello, thank you for this code, it helped me a lot !
However, i was wondering, is it possible to create a pivot table from this kind of list ?
Thanks
- Reply
To post as a guest, your comment is unpublished.
James · 1 years ago
How can i get the code to allow the same selection more than once in the drop down?
Thanks for any help
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  Hi James,
  Kutools can help you solve the problem with ease. You can download a free trial to have a try.
  - Reply
To post as a guest, your comment is unpublished.
Claudia · 2 years ago
The code is working, so it adds all the choices that I have from my drop down list. However, if I have already chosen three of them, how can I remove one because I have made a mistake?
For example, I have chosen (one, two and three) and I want to remove "two" by clicking again "two" from the drop down list. It allows no repetition and remove this one.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  Hi Claudia,
  The code can't be modified to solve this problem.
  You can try the Kutools feature. It meets all your needs.
  - Reply
To post as a guest, your comment is unpublished.
· 2 years ago
I know this may be a total random, but I use a variation of the VBA with no issue. Except on one page, if you select the first three options, it will not let you select the fourth. It will let you select the 5th, 6th, and so on, just not the 4th option. Thoughts?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  Hi Robert,
  I have tested the code but haven't found the problem as you mentioned. Can you tell me which Excel version are you using? Thank you for your comment.
  - Reply
To post as a guest, your comment is unpublished.
Okot · 2 years ago
I cannot proceed with creation of the multiple selection of the drop down list. I have listen to tutorial and read material but still unable to create. Kindly can someone help me out?
- Reply
To post as a guest, your comment is unpublished.
Eni · 2 years ago
Hi, ich bin totaler VBA Laie. Ich versuche den Code so zu modifizieren, dass
a) die Mehrfachauswahl nicht in allen, sondern nur ein zwei Spalten aktiv ist
b) ich Items auch wieder rausnehmen kann, zB in dem ich in der Listenauswahl das Item noch einmal anklicke (Beispiel: ich habe über die Mehrfachauswahl ausgewählt: A, D, X, Y... nun fällt mir auf, dass D nicht dazu gehört. Beim aktuellen Code müsste ich Eingaben entfernen und neu auswählen).
Danke im Voraus!
- Reply
To post as a guest, your comment is unpublished.
wendy · 2 years ago
I'm using the code below to allow multi-select on multiple worksheets but when I go to another worksheet in the workbook the multi-select goes away. When I save the file and come back in it will work for one tab with the code but again when I click on another tab with the code it no longer works. Any idea how to fix it so if i click on a worksheet with the VBA code it will always allow multi-select?
- Reply
To post as a guest, your comment is unpublished.
Randy · 4 years ago
I'm trying to create 4 columns with drop down lists where I can select multiple values. How do I modify the "drop down list with multiple selections" VBA code so that when I click on a value that has already been entered it removes it from the cell? Thank you in advance.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 4 years ago
  Dear Randy,
  What do you mean "when I click on a value that has already been entered it removes it from the cell?"
  - Reply
  - To post as a guest, your comment is unpublished.
    Dez · 3 years ago
    I have the same question. My drop down list does not remember values selected. If someone clicks on a cell that has already been populated (not by them, but someone else) the selected values are cleared and the cell is blank again.
    - Reply
To post as a guest, your comment is unpublished.
Johnna · 4 years ago
I created a drop down list where multiple text selections could be chosen such as "nutrition" ,"weight", and "work" for each caller's reason to phone in. I have a summary page where I want to see how many of each reason were indicated in a particular month. What formula would I use to tell Excel to pull out and tally each of these separately in a given month? Currently, the way I have it set up, it only tallies correctly if I have one reason in the cell for each caller.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 4 years ago
  Good Day,
  Sorry can't help you solve this problem. Please let me know if you find the answer.
  - Reply
To post as a guest, your comment is unpublished.
Nancy · 4 years ago
I managed to use this code and successfully create multiple selection drop down boxes. It worked when I closed and re-opened on different days. However, now not all of the cells I originally selected are allowing multiple selection. Only ones done previously, despite using the code for the whole spreadsheet. Can you help?
- Reply
- To post as a guest, your comment is unpublished.
  yesenia · 4 years ago
  the cells are most likely locked, right click on all of them, go to format cells, protection, then uncheck the locked cell option
  - Reply
- To post as a guest, your comment is unpublished.
  · 4 years ago
  I'm having the same problem.
  - Reply
To post as a guest, your comment is unpublished.
Desiree · 4 years ago
Hi all,

I could do my drop down list perfectly, but my question is: when I select all the items nedded it goes one after another in an horizontal way through the cell, for example: yellow, green, black, red. But how can I make it look in a vertical way?, more like for example: Orange
blanck
yellow
Red
Because in horizontal the cell becomes pretty long when selecting lots of items.

Could you please tell me if there's any way to do this?.

Thank you,

Desiree
- Reply
To post as a guest, your comment is unpublished.
Chloe · 4 years ago
Hi all,

I have this code on an excel sheet and its cleaning the contents from the drop down list when the cell is selected - I know what part of the code is doing it (the part that says 'fillRng.ClearContents') and I have tried to use some of the above to fix it unsuccessfully... I am new to VBA programming etc. Can anyone offer any help on how to change it so that it when the cell is selected it doesn't clear and entries wont be duplicated please??

Option Explicit
Dim fillRng As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Qualifiers As MSForms.ListBox
Dim LBobj As OLEObject
Dim i As Long

Set LBobj = Me.OLEObjects("ListBox1")
Set Qualifiers = LBobj.Object

If Target.Row > 3 And Target.Column = 3 Then
Set fillRng = Target
With LBobj
.Left = fillRng.Left
.Top = fillRng.Top
.Width = fillRng.Width
.Height = 155
.Visible = True
End With
Else
LBobj.Visible = False
If Not fillRng Is Nothing Then
fillRng.ClearContents
With Qualifiers
If .ListCount 0 Then
For i = 0 To .ListCount - 1
If fillRng.Value = "" Then
If .Selected(i) Then fillRng.Value = .List(i)
Else
If .Selected(i) Then fillRng.Value = _
fillRng.Value & ", " & .List(i)
End If
Next
End If
For i = 0 To .ListCount - 1
.Selected(i) = False
Next
End With
Set fillRng = Nothing
End If
End If

End Sub
- Reply
To post as a guest, your comment is unpublished.
Ramon · 5 years ago
Hi there,

Code works fine. However, I can't seem to deselect an item. When I want to remove an item from the selection, it's just not removed. Does anybody else experience this problem too?
- Reply
- To post as a guest, your comment is unpublished.
  StPaulSue · 4 years ago
  delete the content in the cell, then reselect
  - Reply
- To post as a guest, your comment is unpublished.
  THG · 5 years ago
  Was there a response to this issue. It is the same issue I am having. There doesn't seem to be a way to remove an item that has been selected.
  - Reply
To post as a guest, your comment is unpublished.
Charity · 5 years ago
This works well, but I am unable to remove an item once selected. Any suggestions in case I click on something accidently and need to remove it without (hopefully) clearing the whole cell and starting over?

Also, for those seeking to define a column or columns, Contextures has a great addition to the code provided here that allows you to do that.
http://www.contextures.com/excel-data-validation-multiple.html#column
- Reply
- To post as a guest, your comment is unpublished.
  Nirmala · 5 years ago
  [quote name="Charity"]This works well, but I am unable to remove an item once selected. Any suggestions in case I click on something accidently and need to remove it without (hopefully) clearing the whole cell and starting over?
  
  Also, for those seeking to define a column or columns, Contextures has a great addition to the code provided here that allows you to do that.
  http://www.contextures.com/excel-data-validation-multiple.html#column[/quote]
  
  Code works fine. However, I can't seem to deselect an item. When I want to remove an item from the selection, it's just not removed. Does anybody else experience this problem too?[/quote]
  
  Hi all,
  
  Any solutions found for this problem..please share..
  - Reply
To post as a guest, your comment is unpublished.
stef · 5 years ago
Hi I am currently using this formula and all columns with data validation have the multiple selection option now, however I want to restrict the multiple selection only to one column. Can someone edit this formula for me so the multiple selection can be applied only to Column4? Thanks :)

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated: 2016/4/12
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, xRng) Is Nothing Then
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 "" Then
If xValue2 "" Then
If xValue1 = xValue2 Or _
InStr(1, xValue1, ", " & xValue2) Or _
InStr(1, xValue1, xValue2 & ",") Then
Target.Value = xValue1
Else
Target.Value = xValue1 & ", " & xValue2
End If
End If
End If
End If
Application.EnableEvents = True
End Sub

Any assistance will be appreciated!
- Reply
To post as a guest, your comment is unpublished.
Mervyn · 6 years ago
@Cynthia,

If still required, you should be able to do something like this to only ensure the code runs on specific columns, in my case, column 34 and 35:

If (Target.Column 34 And Target.Column 35) Then Exit Sub

'Put this code at the beginning after your dim statements
- Reply
- To post as a guest, your comment is unpublished.
  Dhina · 4 years ago
  If Target.Column <> 34 Then Exit Sub
  
  'Put this code at the beginning after your dim statements
  - Reply
- To post as a guest, your comment is unpublished.
  CynthiaB · 5 years ago
  [quote name="Mervyn"]@Cynthia,
  
  If still required, you should be able to do something like this to only ensure the code runs on specific columns, in my case, column 34 and 35:
  
  If (Target.Column 34 And Target.Column 35) Then Exit Sub
  
  'Put this code at the beginning after your dim statements[/quote]
  
  Hi @Mervyn,
  
  Lost track of the thread completely, but thank you so much for your responses.
  
  I've tried applying the
  If (Target.Column 34 And Target.Column 35) Then Exit Sub
  (my version reads If (Target.Column4 And Target.Column5) Then Exit Sub
  as you supplied, but am getting a "Run-time error '438': Object doesn't support this property or method"" error on this new line.
  
  Here are the first few lines of my code:
  
  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim xRng As Range
  Dim xValue1 As String
  Dim xValue2 As String
  If (Target.Column4 And Target.Column5) Then Exit Sub
  If Target.Count > 1 Then Exit Sub
  
  On Error Resume Next
  
  My worksheet only has 6 columns: Question | Answer | Category | Sub-Category | Tags | Photo link
  I only need multiple value drop downs in Sub-Category and Tags (columns 4 & 5).
  
  I'll keep looking for info as you suggested on 12/23, and will look at the link Charity provided.
  - Reply
To post as a guest, your comment is unpublished.
Mervyn · 6 years ago
Hi Cynthia,

If the original author doesn't reply, I'll get you an answer but I'll only be in front of a computer on 29 Dec again. I'm also no VBA programmer. What you can do in the mean time is Google search how to identify the column number and only let the code run if data is edited in that specific column(s). I've done it but the code is on my work PC and can't recall it at the moment,maybe try putting a debug.print on target.column or something to that effect to see if it gives you the column number being edited.

Sorry Jennifer, not sure about the issue you're having :(
- Reply
To post as a guest, your comment is unpublished.
Jennifer L Price · 6 years ago
I was able to get the code to work, but then when I saved the document (with macros-enabled), closed it and returned, the code didn't work anymore (though it was still in there). I can't figure out what I've done wrong. Any ideas?
- Reply
To post as a guest, your comment is unpublished.
CynthiaB · 6 years ago
Hi. Thank you for the code and the addition to limit duplicates.

One more request - what addition/change would have to be made in order to allow multiple selection in only one or two specific columns? This code is re-adding lines of text to what should be 'plain' cells if I go to correct a typo, or make a change or addition to the text in the cell, as opposed to just behaving 'normally' and accepting the change (without re-adding the entire text again).

For instance, column A is a 'plain' column. I write a sentence "What are the three itmes you want most?" Column B is a 'list' column where I only want to be able to pick one single value (in this case, let's say a child's name). Column C is another 'list' column where the user must be able to select multiple items (which this code allows me to do perfectly).

As I go along, I realize that I've made a typo in column A and want to correct it. As this code stands, if I go in (double click, F2) and make the correction to the word "items", I end up with this result in my cell:"What are the three itmes you want most? What are the three items you want most?"

thank you in advance for any help (from a user that REALLY likes VBA, but is still at the very earliest stages of learning!)
- Reply
To post as a guest, your comment is unpublished.
Mervyn · 6 years ago
Just realised I didn't exit the loop in the new function if the condition has been set so we don't have to check other entries.
- Reply
To post as a guest, your comment is unpublished.
Mervyn · 6 years ago
You can change the code in the following lines to prevent the duplicates:
If xValue2 "" Then
Target.Value = xValue1 & ", " & xValue2
End If

To:
If xValue2 "" Then
If CheckIfAlreadyAdded(xValue1, xValue2) = False Then
Target.Value = xValue1 & ", " & xValue2
Else
Target.Value = xValue1
End If
End If

And then add the following function:
Private Function CheckIfAlreadyAdded(ByVal sText As String, sNewValue As String) As Boolean

CheckIfAlreadyAdded = False

Dim WrdArray() As String
WrdArray() = Split(sText, ",")

For i = LBound(WrdArray) To UBound(WrdArray)
If Trim(WrdArray(i)) = Trim(sNewValue) Then CheckIfAlreadyAdded = True
Next i

End Function

--
There's probably better ways of coding it but it works for now.
- Reply
To post as a guest, your comment is unpublished.
MichaelB · 6 years ago
It is great that this allows multiple selections but like @Yezdi commented, I am finding it will add one or several duplicates even if I don't choose them.

So, at present, this is an 80% solution... one tweak away from perfect. I am not a VB coder or I'd offer the solution.
- Reply
To post as a guest, your comment is unpublished.
Yezdi Eks · 6 years ago
Hi,

Thanks for the solution and the code.

But the next step is how to make sure that the user
does not select "duplicate" values from the dropdown list.

E.g. If there are 4 items in the list -
orange, apple, banana, peach

and if the user has already selected "orange", then excel
should not allow the user to select "orange" OR that option
should be removed from the remainder of the list.

Can you please publish the code to accomplish this feature.

Thanks.

Yezdi
- Reply
- To post as a guest, your comment is unpublished.
  sunshine · 5 years ago
  Hi Yezdi,
  Thank you for your comment. The code was updated and no duplicate values allow in the drop-down list now.
  
  Thanks.
  
  Sunshine
  - Reply