How to Make Multiple Selections in a Drop-Down List in Excel - No Duplicates Allowed - VBA Code inc (2023)

Introduction

Download the featured file here: www.bluepecantraining.com/how-to-make-multiple-selections-in-an-excel-drop-down-list/

Excel now blocks macros by default when you download a workbook from the internet. Therefore none of these macros will run in the workbook I have provided unless you unblock the macros. Please see this page for guidance learn.microsoft.com/en-us/deployoffice/security/internet-macros-blocked.

This video answers the following queries:
How to achieve drop down multiple selection in Excel
How to create Excel drop down list allowing multiple selection in same cell
Can you select multiple items in an Excel drop down list?
How do you select more than one item in a list?
How do I put multiple items in one cell in Excel?
How do I select more than one drop down option?
How to make multiple selections in a drop down list in Excel
Data validation drop down list multiple selection without duplicates
VBA code for multiple selection drop down
How do you do multiple choices in Excel?
------------------------
All enrolments and purchases help this channel (a commission is received at no extra cost to you):

😎5 FREE Excel Templates😎
www.simplesheets.co/a/2147506566/v7Aj9zMx

😎Enrol in the Excel University – FREE taster course available😎
www.simplesheets.co/a/2147491676/v7Aj9zMx

😎Get 30% Discount on Simple Sheets Templates and Courses
www.bluepecantraining.com/simple-sheets-templates/
Use Discount Code BLUE😎

Content

Hi, it's chester, tugwell at blue peak and computer training, and in this video I'm going to show you how to create drop-down lists where you can select multiple items.

This functionality will need some code and I've left a copy of the code in the description of this video.

First of all, I'll show you how to create the drop-down list.

I've entered the values that I want to appear in the drop-down list on a separate sheet called names, and to make this really easy to refer to I'm going to name this range of cells to do that.

I've selected the sales and then I go up to the name box up here and I'm going to call this name underscore list.

I can't have a space in the name: press enter and I've named the list, I'm going to go to a sheet where I want to create the drop-down list.

If I select the cell, I want to enter the drop-down list in I go up to the data tab on my ribbon.

Go over to data validation and I allow a list and then in the source box.

I have to enter the name that I gave that range earlier on now you can either type it in or what I tend to do is press f3 on my keyboard and select the name in this dialog box click on ok, and now I get my drop down list.

The problem with this drop-down list is that if I select another name, it replaces the existing name.

So we need to add the code to our worksheet to get the extra bit of functionality to do that, you need to open the visual basic editor.

The easiest way of achieving that is to use the shortcut key alt f11.

This is the visual basic editor and you need to make sure that you can see the project explorer.

If you can't go to view project explorer within the project, explorer select the sheet that you're currently working in then over in the code window paste in the relevant code.

I've already got that on my clipboard and I'm pasting it in for this worksheet.

I don't need to change anything in the code, so I can just close down the code window and now, if I select new names, it adds the names to the list rather than replacing existing names.

If I select a name, that's already in the list, it doesn't try and add it again in this example, is piling the names on top of each other, but you might want all the names in a single line separated by commas.

Let's see how we can achieve that, I'm going to go to the next sheet.

What I'm going to do is create the drop-down list, data, tab, data, validation, list, f3 and paste in name list click on ok.

Now I have my drop down list I'm going to reopen the visual basic editor called f11 and I'm going to look at the relevant line within the code that in our previous example piled the names on top of each other and the relevant part of the code is down here and what you can see here is it's concatenating or joining two values an old value and a new value and the bit that we're interested in is this word here vb newline that essentially creates a line break between the old value and the new value I'm now going to switch over to the code that I've pasted in for the comma separated sheet and if I look down to the same line of code you can see that instead of using vb newline I'm using a comma and a space between the old value and the new value that's the only change I've made the rest of the code is the same now if I select multiple items.

You can see.

That it enters them all on the same line.

Separated by a comma with the code as it is, if I created a drop down list in another cell, see that the functionality is not there, functionality is only working for a particular cell on the sheet.

What if I wanted the functionality to be applied to a range of cells and switch to the next sheet, I want the functionality in all three of these cells.

First of all, I need to put the drop-down lists in each of these cells, and I can do that to all the sales in one go.

I've selected all the sales got to data validation list paste in my name now I have a drop down list in each of these cells.

I'm going to open up the visual basic editor, I'm going to go back to the previous example.

We were looking at where we added a comma between the names and the bit of code that specifies which cell this functionality applies to is in this line here and specifically range a2.

If I wanted the code to apply to a2, b2 and c2, I just need to make a very slight change.

I've already done that in range of cells, this next sheet, instead of just a2 I've, said a2 colon c2.

So let's see how this works.

I can choose multiple names in any of these cells.

However, sometimes you might want the functionality to apply to a whole column.

You don't know how many cells you might eventually use within a column.

Let's see what changes we need to make to the code to allow for that.

Back to my visual basic editor, I'm going to go to the whole column object, and here I've made a change to the same line of code as before, but to specify a whole column.

You can use this syntax b, colon b.

If you wanted it to apply to a whole row, then you could put the row number in.

So if I wanted row 2 to have this functionality, I could put two colon two: let's see, if it does work, apply the drop downs you can see in any cell in this column I have that functionality.

Another use of this code is to use it without drop downs at all.

Let me show you what I mean I'm going to go to the next sheet.

I want to be able to type a name into a cell and for that name to be added to an existing list within that cell.

Let's see what changes we need to make to the code to allow for that back into the visual basic editor and I'm going to go into this sheet.

The first thing I'm going to do is change the range of cells that this applies to the whole of column b, then directly, underneath the line that we've just edited you'll see two lines of code which we don't need.

These two lines ensure that this functionality only applies to cells that contain a data validation.

Well, we don't want that to be the case, so I'm going to delete those two lines, because we deleted that.

If statement, we need to delete the relevant end if further on down in the code, we also need to delete else colon here in this line of code.

That's all the changes.

We need to make close down the visual basic editor and, let's see how this works.

First, I'll type bob into the cell and I'll press enter and one thing you'll notice.

Is it automatically stays in the cell I'm going to type over bob with bill and press enter, but what it does it doesn't replace bob.

It just adds bill now, I'm going to type over bob and bill with brenda press enter, and it adds brenda to the list.

Okay, that's all I wanted to cover in this video.

Hopefully you found this useful.

If you have please subscribe and I'll see you next video.