Select Multiple Items in an Excel Drop Down List - Easy VBA Trick Anyone Can Use

[@jjcamachosanchez]

Thanks! I truly value that you explain the code. Others simply show the code, but you make sure that your audience understands what is happening

[@MyOnlineTrainingHub]

Great to hear! 🙏

[@kensleylewis]

Why is this the only video on KZbin that talks about apply multiple pre-selected options into a single Excel Cell. Excellent work.

[@MyOnlineTrainingHub]

Probably because it's not best practice to stuff multiple criteria into a cell 😉 but glad we could help.

[@kensleylewis]

@@MyOnlineTrainingHub I can see truth in that. Many thanks.

[@srezanoori]

Clear, concise, and to-the-point. Thank you.

[@MyOnlineTrainingHub]

So pleased you enjoyed it, Reza!

[@AngelaSpring]

Hi Mynda, Thanks for this video. It helped me a lot :) Wanted to ask how you would add another multi-select list to the worksheet/VB? So say one multi-select list is for Contacts (Ie, Tom Smith, Karen Wood) and then in the next column (to the right) it contains a multi-select list for Class format (In class, Online). Is it even possible? Thanks!

[@mistyboni5862]

Thank you so much for this! I have been trying to find a way to do this for months!

[@MyOnlineTrainingHub]

Glad I could help!

[@IvanCortinas_ES]

It is very practical. Well, it allows us to see, for example, the names of two people who have participated in a process (both of them) and we can use data validation for this. Thank you for this great contribution Mynda!!

[@MyOnlineTrainingHub]

So pleased you can use it, Ivan :-)

[@meriemziane2424]

Hello :) Very usefull video. I have a question though. I would like to apply this code for multiple column in my excel file. How do you duplicate it to work for the other columns also ? I managed to do it only for one as you explained in you video. Thanks for your answer !

[@nicholechristoph56]

This is exactly what I was looking for to clean up a spreadsheet! One question...how would the code change if you wanted to apply the same feature to more than one column?

[@martystone8789]

Hi, there’s another Video describing it: “How to Create A Multi-Select Drop-Down List in Excel” by Sharon Smith. Just found it for my own use as I had the same question. Kind regards

[@lekhraj6809]

Excellent Mam, i will try this. Is it possible without VBA coding?

[@MyOnlineTrainingHub]

No.

[@JosLuisOfficial]

Thanks for the tutorial, very useful just one question, how to select multiple values? I tried with Ctrl+ shift combination but it don’t worked :(

[@MyOnlineTrainingHub]

You select them one after another i.e. select one, then click on the drop down again and select the next one.

[@SumitNandy-rn1nf]

Great!! Is there a way that I can press a button to to pop-up a drop-down where I can have multi-select and once clicked outside, it updates the cell with multiple values with a separator?

[@MyOnlineTrainingHub]

Probably, but I don't have a tutorial I can point you to that will explain how. If you get stuck trying it yourself, you can always post your question in our Excel VBA forum: www.myonlinetraininghub.com/excel-forum

[@yquintana5673]

This is great! How can I still make the items be counted separately when I insert the data into a chart? Right now, it counts all items in the one cell as one category.

[@MyOnlineTrainingHub]

Not easily, which is why Excel wasn't designed to have multiple items in one cell. You would have to split them again and then count. You could use the new TEXTSPLIT function wrapped in COUNTA: www.myonlinetraininghub.com/textsplit-textbefore-and-textafter-functions

[@rubendominguez5564]

Thank you very much for this tutorial! This is wonderful, clean, easy and organized! I loved it!😊

[@MyOnlineTrainingHub]

Great to hear, Ruben!

[@brucehardy1640]

That was very nicely done. Thanks.

[@MyOnlineTrainingHub]

Thanks so much, Bruce :-)

[@AdvanceOffice]

Very informative mam, i tried making a drop down list tutorial especially in hindi.

[@MyOnlineTrainingHub]

Glad you liked it :-)

[@AdvanceOffice]

@@MyOnlineTrainingHub just cause this amazing tutorial if by you mam :)

[@jessical3746]

Hi, would like to know more about what I should do if I want to have the multiple selection shown in separate rows. For example, instead of “Apple, Banana”, I want to show it as “Apple, Banana” Thank you!

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@lalaplectann985]

Hello Mynda, Very helpful for dimensions, what about for dates? Is there any way to link this to an array for dates? For example, If I wanted the user to select multiple dates and get relevant data only for those dates - if my drop-down consisted of weeks of the year and I select multiple weeks and return --let's say--Total sales for those weeks using SUMIFS - where one of my criteria could be the "multi-select" weeks drop-down? Thanks.

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@bernibrand3724]

Great explanation and was able to implement it on my worksheet, however, prior to this had a VBA code to make the cell with the drop down list better readable by having it zoom once cell is selected. Now the trouble is, that either I can work with your VBA code for multiple selection or VBA code for better reading, but unfortunately not booth together working. Is there a workaround or some tutorial to learn about this combination? Thank you in advance

[@wayneedmondson1065]

Hi Mynda.. very clever.. VBA to the rescue! Thanks for sharing. Thumbs up!!

[@MyOnlineTrainingHub]

Cheers, Wayne! Sometimes only VBA will do :-)

[@EricHartwigExcelConsulting]

Such a great video! Thank you for sharing this with everyone!

[@MyOnlineTrainingHub]

Glad you enjoyed it, Eric!

[@brianryerson7484]

Thank you for the great video and this has been very helpful to me. I have I hope a quick questions. Instead of the "," between each selection is there a way to use the "vbNewLine" and have them on the separate lines? I tried to mod your code but I can not seem to get it to work right. Thanks again.

[@martystone8789]

Hi, there’s another Video describing it: “How to Create A Multi-Select Drop-Down List in Excel” by Sharon Smith. Just found it for my own use as I had the 😂same question. Kind regards

[@fyrweorm]

Thank you for the code explanation. Is it possible to adapt this code to do multiple selections in dropdown lists inside an excel table (listObjects)? I don't have enough knowledge to do it by myself

[@MyOnlineTrainingHub]

You can, but it's not really suitable in a table.

[@flomast3r]

How do you use vba with a two column spreadsheet? If i want vba to check cell A1 to see if it contains a value matching any in the first column, and then scroll to the cell listed in corresponding row of second column?

[@sahildawany1032]

Great tutorial! Very informative. Thank you!

[@MyOnlineTrainingHub]

My pleasure, Sahil!

[@sahildawany1032]

@@MyOnlineTrainingHub I noticed that if you have multiple options in the list that have common words, the script removes the common word. For example, I have a list with various PCR test types (COVID-19 PCR, DDPCR, RT-PCR & PCR), I can choose the first 3 without any issue (cell content shows as COVID-19 PCR, DDPCR, RT-PCR) but if I try to select PCR after selecting the 3, the script removes the "PCR" from the previous entries and the cell content becomes (COVID-19 DDRT-PCR). Would you be able to help me overcome this challenge?

[@subramanirangasamy3320]

More learning thanks you very much

[@MyOnlineTrainingHub]

Glad to hear that :-)

[@muratucar227]

Thank you. Good technic for multiple selection. Congratulations.

[@MyOnlineTrainingHub]

Many thanks!

[@adriecoetzee4301]

I need this function to work in a protected worksheet. How can the VBA code be adjusted to prevent the multi-select to disappear once the sheet is protected?

[@MyOnlineTrainingHub]

You'd have to unprotect the cells the data validation lists are in.

[@omarnemo7732]

hey there. how to make it possible on multiple columns or ranges? If Not Intersect(Target, ActiveSheet.Range("J:J")) Is Nothing Then here is J column is effected by vba code. but how to make and N column also possible to select multiple items.

[@mihajlovicrus]

Hi Mynda, Thanx for tutorial. What if I have depended drop down list, it is not working in my file, is there a solution to it ? I have 1 independed and 2nd depended cell based on first one ? It is showing there is Bug ? Appreciated in advance.

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@nayanamurthy28]

Thank you so much for your video it very helps full for me...

[@MyOnlineTrainingHub]

You are most welcome, Nayana!

[@knunne1]

Great video, I will put it to work today. Thank you!

[@MyOnlineTrainingHub]

So pleased it's useful to you!

[@stephaniedaou5228]

Thank you for the tutorial. I have a question, if i want to have this option on many cells not just one, how can i proceed? Thank you

[@MyOnlineTrainingHub]

You can copy the cell containing the list and paste it.

[@darrylmorgan]

Hi Mynda!Great Bit Of VBA..Thank You :)

[@MyOnlineTrainingHub]

Cheers, Darryl!

[@shakiraasfoor7599]

Well Done Myanda

[@MyOnlineTrainingHub]

Thank you!

[@GunGirlYuna]

If you click the same thing twice will it be in the cell twice?

[@MyOnlineTrainingHub]

No, there is a line of code that prevents that, but you can remove it if you prefer. It's all commented in the Excel file (download link in the video description) so you can see exactly what I'm referring to.

[@dashrathpanchal8393]

Hi..very unique ...great !! thanks for sharing

[@MyOnlineTrainingHub]

:-) thanks, Dashrath!

[@EngineerGen]

Is there a way to make it so that when i selected a text that is already included/selected before it will remove that text?

[@MyOnlineTrainingHub]

Hi Gene, probably, but I don't have a tutorial I can point you to. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@MukunthanIyer]

This is great. But, once we capture data with multiple values, can we pivot them once again back with alpha, bravo, charlie individually? Say, in this current example, the count us rows with 'Alpha, Bravo' will be 1. Instead, can we show it as Alpha: 1, and Bravo: 1?

[@MyOnlineTrainingHub]

Glad you liked it. I think the moral of this story is that you shouldn't really store your data like this because it prevents you later making use of it in formulas :-) If counting it is the end goal, then store the values in individual cells. If you want your cake and eat it too, then use Power Query to split the data by the comma delimiter into rows where it can then be counted.

[@waltermakuzander5248]

I just want to ask question about drop down list, I hope you could help me. Is there a way that can be a warning sign or a message once I change the first cell (with drop down list, say Quarter 1 to Quarter 2) that I need to change the next cell (with dependent drop down list)? Given that Quarter 1 (January, February, March) Quarter 2 (April, May, June) I suddenly change the Quarter, is there a function that will tell me to change the month inclusive to that quarter? Please help 😊

[@MyOnlineTrainingHub]

No, there's no way to do his with the built in functionality of Excel. You'd need to edit the VBA code.

[@lumierenk5652]

great !!! but what to do if the excel is online (in sharepoint) ? please advice

[@MyOnlineTrainingHub]

You'd need to open the file in the desktop Excel app. VBA cannot be executed online.

[@lumierenk5652]

@@MyOnlineTrainingHub yesssss it works thank you sooo much

[@farzanmoha8935]

Thank you Myanda, I am thinking where can I use this multiple selection in my reports or any dashboard.

[@MyOnlineTrainingHub]

Glad you'll be able to make use of it, Farzan :-)

[@chrism9037]

That was great, thank you!

[@MyOnlineTrainingHub]

Glad you enjoyed it, Chris!

[@eeminchiam]

Hi, I have a question which is I would like to add a title infront of the selection, for example a cell name Fruit that contain "Apple, Banana, Orange" and a cell name Vegetable that contain "Broccoli, Cabbage, Lettuce". But the Fruit and Vegetable cannot be selected. What am I meaning is that Fruit and Vegetable is basically like a header for those selection. I hope my question is clear enough. Thank you.

[@MyOnlineTrainingHub]

I think what you're after is dependent data validation lists: www.myonlinetraininghub.com/excel-dependent-data-validation

[@eeminchiam]

@@MyOnlineTrainingHub Hi, thank you for the respond. I wish to done this in only one column, is that possible? Thank you.

[@LuisFernando-yd3mx]

How do we change the code so that it places the other options selected on each additional rows below and not on the same cell.

[@MyOnlineTrainingHub]

If you don't need them all in the one cell, then simply use a regular data validation list in each cell: www.myonlinetraininghub.com/excel-drop-down-lists

[@merbouni]

Hi Mynda, thank u for this great video, it's a very useful idea for some works. But I think we can only use this if we have a short and limited items in the data validation list (10 items in max for example). The problem is..... what if we have a lot of items to select, and we choosed a wrong item..? in this case, we cannot undo or edit the content of our DV cell by pressing F2 Key or with any other way..! Mynda, i think the VBA code needs some modifications, I'm waiting for your solution, why not, maybe in your next video.

[@MyOnlineTrainingHub]

Glad you found it useful. A workaround is to copy the incorrect selections to another cell, edit it, then copy it back.

[@merbouni]

@@MyOnlineTrainingHub Well, I expected this solution, " Look for a simple way to fix bugs " ;)

[@omarnemo7732]

@@merbouni there is already update to select again and item will be deleted

[@johnborg5419]

Thanks Myndy. :) :)

[@MyOnlineTrainingHub]

My pleasure, John :-)

[@shanawazbashi8037]

Mam i follow u on KZbin. Im struggling to find a way to solve this issue. Is it possible to use Formula and Data Validation in the same cell...? As i want to call a product by formula and also give option to choose another product by DV . Please help in this regard. Or refer a video

[@MyOnlineTrainingHub]

Not sure what you're wanting to do, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@cryptodon0074]

HELP I have done everything you said but I am getting an error on the line code below?? If Not Intersect(Target, ActiveSheet.Range("DV_Range")) Is Nothing Then

[@MyOnlineTrainingHub]

Have you downloaded the sample file and compared it to yours? If so, please post your question and Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@luwannaclark5356]

Hello, I'm confused, I copied the code into my workbook but it didn't work. Am I supposed to change the settings in the code and if so, where?

[@MyOnlineTrainingHub]

Hi Luwanna, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@7ofEverthing]

Thank you so much. But I run into problem when I tried to delete one name, for example, I selected three names but I decided to delete the third one, this will give error. It will only allow to delete all instead. Anyone encounter this?

[@MyOnlineTrainingHub]

Hi Joel, if you select the name again from the list it removes it from the cell.

[@okezienwachukwu6272]

The issue is when I apply this code it makes all drop down lists within the table multiple selection. How do I ensure it works for just a selected column?

[@MyOnlineTrainingHub]

Hi Okezie, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@JatinKumar-kg8oh]

Can wo do this in sheets?

[@MyOnlineTrainingHub]

Sheets doesn’t have VBA, so I’m not sure how you’d go about it.

[@crips192]

Thank you Ms.

[@MyOnlineTrainingHub]

My pleasure :-)

[@phenomenon8716]

I don't know what I'm doing wrong. I copied the code, and I'm still only able to select one item from the drop-down...

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@tarekhemaida7072]

How Can we use this code for multiple cells or column..

[@MyOnlineTrainingHub]

Hi Tarek, If you download the sample Excel file available from the link in the video description, you'll see it has been used in multiple cells in that file. Hope that points you in the right direction. If you get stuck, please post your question and sample file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@rohithk.7991]

is this possible in Google Sheets?

[@MyOnlineTrainingHub]

Don't think so, Rohith.

[@عبدالرحمنالجابري]

Thanks 😊

[@MyOnlineTrainingHub]

You're welcome :-)

[@feshii]

Thank youuu

[@MyOnlineTrainingHub]

You're welcome!

[@merrisahakyan2016]

Alt+F11, does not open the code on my windows 10, I have tried Fn+Alt+F11 as well, but this open Visual Basic.

[@MyOnlineTrainingHub]

Hi Merri, Visual Basic is the window you should be opening.

[@davidhoneger113]

thank you

[@MyOnlineTrainingHub]

You're welcome, David!

[@ajmainhossain780]

Would you please give me a copy of the code in this box ....that would be a very good help for me dear

[@MyOnlineTrainingHub]

The link to the Excel file containing the VBA code is in the video description.

[@pennyanderreeperbahn8360]

For some reason, after restarting the excel sheet, your VBA code doesn't work anymore. It is still there and everything but the multi select part doesn't work. Anyone knows what do to? Edit: Maybe the VBA code is not running automatically?

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@amdanielgaming]

How to do the same on sheets

[@MyOnlineTrainingHub]

I don't use Sheets, so I'm not sure.

[@hardnewstakenharder]

I’m sorry, but this is still extremely confusing to me. I have no idea what is happening when I press Alt-F11, nothing there makes sense to me and I have no idea how I’m supposed to apply all that code to my own excel spreadsheet. Could you do another video that explains this as if it were explained to a five year old?

[@MyOnlineTrainingHub]

Please see this tutorial that explains what to do with code you find on the internet: www.myonlinetraininghub.com/how-to-use-vba-code-from-the-internet If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@hardnewstakenharder]

@@MyOnlineTrainingHub Thank you!

[@danjky2774]

I open VB but see nothing, why is that?

[@MyOnlineTrainingHub]

You haven't created any code yet?

[@pratikpalette]

I need a way to make multi-dropdown list without changing my file's extension. No-one in youtube has tried so , how lame

[@MyOnlineTrainingHub]

They haven't tried because it's simply not possible.

[@rameezkruddson7567]

😘😘😍

[@MyOnlineTrainingHub]

Glad you liked it :-)

[@jujuiscool6206]

It's not working for me :(

[@MyOnlineTrainingHub]

Sorry to hear that. You can post your question in our forum and we can try to help you there: www.myonlinetraininghub.com/excel-forum

[@phvaguiar]

Cool

[@MyOnlineTrainingHub]

Thanks, Pedro :-)

[@dannyleblanc336]

i can not undertand why multiple items in a drop down list it's not offer in Excel whitout VBA!!?? But thank's for this code ;)

[@MyOnlineTrainingHub]

Glad it was helpful, Danny!

[@GreenTheChain]

this would be great if the code was supplied

[@MyOnlineTrainingHub]

The code is supplied. There is a link in the video description to where you can download the example file and code. Here it is again: www.myonlinetraininghub.com/select-multiple-items-from-drop-down-data-validation-list

[@leonardogranados2733]

Nice but too complicated for me

[@MyOnlineTrainingHub]

Personally I don't recommend this technique, but we get asked about it from time to time, hence the video.

[@MuhammadAli-hg1td]

My version of the code which puts the selected items on the same line and separates them by a comma for columns A to Z: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = True On Error GoTo Exitsub If Not Intersect(Target, Range("A:Z")) Is Nothing 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 Also if you're getting an invalid procedure name error, then save the macro as: "macro_select_multp_items_dropdown_sep_by_comma" and save the file in a macro enabled excel file which has an extension of .xlsm on Windows

[@MyOnlineTrainingHub]

Thanks for sharing 😊

[@MuhammadAli-hg1td]

@@MyOnlineTrainingHub You're welcome :)

[@itsharish_008]

Is it possible to create multiple select option to single drop down cell without using a VBA code?

[@MyOnlineTrainingHub]

No.