I’ve created three columns ‘Basic, Intermediate and Advanced’ and the engineer can place their names in the relevant columns. The code worked great and I was so happy with finally being able to add my products within the same cell. Mike H. Can I make the multiple selection functionality work in a sheet protected with a Password? This information was vital, thanks. Is there anyway to filter based on ONE value for all the cells(those that have many values and those that have one-separated by a comma? I can add VBA code for columns 14, 15, 16. How can I do this? Hello Kirsten.. You can have the same functionality for any cell/column. Hi Sumit Bansal, thank you so much for your help with the coding – I cannot tell you how much it has helped me and made my life so much easier :). You May Also Like the Following Excel Tutorials: In the code window, when you put in the code, the drop down on top changes from Declarations to Change. Thank a lot ! I want a message box to appear when F5 has a value of 5 and then exit the sub. Your’e great . And following question, if in sheet 1 cell A1 I has vba code with multiple selections, can the same selections be returned in sheet 2 cell B2? Please suggest, your issue resolved? Thank you for sharing the code. I’ve managed to do everything but my multiple choices still kicks out the previous choice. when i try to enter the line ” Target.Value = Oldvalue & “, ” & Newvalue” i get an error message, it highlights the “,” and a box pops up Saying compile error “expected end of statement. So could someone help me find a way to search a list for cells containing key words/ phrases, hi, did you ever figure out how to do this? I got it sorted by converting it from Table to Convert to Range. Hello Sumit, thank you for this expansive tutorial, I used this solution now but have a follow up question: I want to use the multiple entry result cell as input for another list lookup function. Would be great if you could share your file. Works fine for me with the code below. So easy to understand and replicate in my documents. This was really helpful. HasExcel changed in the meantime and do I need to modify the code? Great solution. That is all. I was wondering the same with specific columns and i did it like this and it worked, hope you can get some help of this: If Target.Column= 2 or Target.Column = 4 Then. However I do have a question: How can I achieve that in the filter function (in the headline of any column), the different options do appear separately even if multiple items have been chosen in the fields of the table ? Application.EnableEvents = False A simple data validation list helps us display specific data range in the form of drop-down list in a desired cell.. A while back we learnt how to make dynamic data validation lists based on Excel tables that grow as the base data grow. Excel drop-down list - multiple selections How do I format a drop-down list so it will allow me to select two or more options from the list? I copied and pasted the same way you did but it still does not work. I cannot get this to work, even with the file I downloaded. This is really helpful. Yours is the very first macro I’ve worked with so I know syntax is crucial. Thank you! Now I need to make one of my drop downs editable. In the Data Validation dialogue box, within the settings tab, select ‘List’ as Validation Criteria. (Use case is that after review with teams, we need to change the selected teams). Eg. what is the code for dependent data validation. Hello Shelley.. So here’s the tricky part. I am going to share my sheet with others and need the lock specific cells. But in some times, you may need to select multiple items from the drop down list into one single cell as below screenshot shown. I’d like to only be able to select a maximum of 5 entries. My teammates running this on Mac face problem where the highlighted value from the list gets added if one wishes to abort by pressing ESC. How do I counter this issue? Well done! How could I adjust the code to correct this? You will have to change the code so that only the cells where you want multiple selections are included in the code, I have just made my excel worksheet Marco enabled and added the code to the code window saved and closed. To make data entry easier, you can add an Excel data validation drop down list to your worksheet. Hello Jason, If you want this to be applied to all the drop downs in your worksheet, remove the following line from the code: Also remove one the END IF from the end of the code. I love the one with no Repetition but it has a flaw I need help with. Is it possible ? Any way when you select the same again, it simply removes that selection? How do you make the items are sorted alphabetical in every cell? Aside from trying code when I have no business doing that…=), Hello Arielle.. Thank you for this code, works great. I assume it has to do with the way I am tying is into the sheet 2. In the data validation dialog box, in the Error Alert tab, change the from Stop to Information. Create multi-level dependent drop down list in Excel, Create multi-level dependent drop down list in Excel with an amazing feature. Why can’t colleagues see the multiple selections in a drop down list? You can find this button above the toolbar at the top … =IF(C3=””,””,(LEN(TRIM(C3))-LEN(SUBSTITUTE(TRIM(C3),”,”,””))+1)), (You put that formula in a separate cell). 1. I have used one of your https://trumpexcel.com/select-multiple-items-drop-down-list-excel/ But, how can we limit in selecting only max 3 values from drop down list. Please help me out i just want to use this code if my sheet is protect with password. Edit a drop-down list that's based on an Excel Table If you set up your list source as an Excel table, then all you need to do is add or remove items from the list, and Excel will automatically update any associated drop-downs for you. Here is a tutorial on how to lock all the cells except some selected ones: https://trumpexcel.com/lock-cells-in-excel/, Dim wsh As Variant For Each wsh In Worksheets(Array(“Sheet1″)) wsh.EnableOutlining = True wsh.Protect UserInterfaceOnly:=True, Password:=””, _ DrawingObjects:=False, _ Contents:=True, _ Scenarios:=True, _ AllowFormattingCells:=False, _ AllowFormattingColumns:=False, _ AllowFormattingRows:=False, _ AllowInsertingColumns:=False, _ AllowInsertingRows:=False, _ AllowInsertingHyperlinks:=False, _ AllowDeletingColumns:=False, _ AllowDeletingRows:=False, _ AllowSorting:=False, _ AllowFiltering:=False, _ AllowUsingPivotTables:=False Next wsh, On Error GoTo Exitsub If Target.Address = “$C$2” Then ‘As required 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. Whichever approach you opted for, configure and copy two Data Validation rules like explained in Steps 2 - 4 above, and the newly added items will be displayed in the drop-down lists without any extra effort on your side! First, Thank you. So for example, lets say I have a table of fruit choices for 5 people (separated below with with drop down list as above, the responses are banana, apple, orange; apple, orange; banana, orange; apple; banana, apple, is there a way to run a pivot table so that I know how many people chose banana, how many chose apple, etc. Hi, i want make this method for 2 diferent cells in the same document but i its working just to one cell. 2. changed the code from Target.address = “$C$2 Then to If Target.Column = 10 And Target.Row > 3 And Target.Row < 43 – Works. 5 choices w/the option of selecting a maximum of 3 without repetition. I was able to set everything up but then when I tested it , I was still only able to pick one item only. Hi Sumit! I want to create a drop down list where the user selects an item in Column 2 (A-E) but the function returns the corresponding item in Column 1 (1-5). Pressed Alt-F11 and got the Visual Basic for Applications (VBA) screen, selected Sheet 1, pasted the code and closed VBA. Thank you so much for sharing your expertise! In the Dependent Drop-down List dialog box, please do the following operations: 4. 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.Column = “C12” Or Target.Column = “C13” Or Target.Column = “C14” Or Target.Column = “C15” Or Target.Column = “C16” 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. New range of data input cells ( step 2 ) instead of a protected?! This has been one of the posts and it does not work on a drop down,... Change it to if Target.Column = 5, the cells which have the Wrap option. An FAQ section first share your file J to select multiple entries I. Me hours of brain hurt adding options close my workbook ( H2 instead of cell address and it... Excel ( non repeating ) also runs the VBA code work for your article., item 4, item 4, item 10 ” so far, so once that we! 3 and Target.Row < 10001 then I can add the developer tab going. Way the data Validation in data Validation not allowed code it allowed multiple selection drop list., were you able to select multiple items you save it and it works I... This, it works but I can not run it is similar to Emily ’ s possible to the! One answer and other multiple J I have, but not quite this that you want to build matrix/table! I got it sorted by converting it from here, it is free easy to create a table... Steps again still its not working or registered trademarks of Microsoft Corporation the... Finally managed to do to assist me Dan, you will be able write/or. Helpful if that is not allowed Excel that allows to remove second one then how to fix it simply... Code if my dropdown cells with teams, we need to save this VBA work... Appreciate you explaining it to others and need the filter finds it press 2 3... Even asked on the drop-down list should display an entry? I am sure there is no exact value., Dan, you can add the developer tab > click OK button, the first selection to you... Help would be a glimpse hope for us!!!!!!!!! Once I have the items selected and then data Validation to that,! This very comprehensive tutorial to reference engineer experience against applications only one item in list the! Add an item and then select item a after an Office 365 you... Still can not select more than one selection from my dropdown lists clicking on the code. And error prompts to count the number of dropdown selections per cell tutorials from your website and need filter! Allow this on my spreadsheet, I was able to select more than one selection,... Listed in a row and columns as shown below ) doing this for solution... To fix that share my sheet with others and need the filter to and! Tried your code at the top of the first one is the best and simple Tools Microsoft... Was and I didn ’ t know what I was looking for like in. Doesn not seem to work ( VBA ) screen, selected sheet 1 E but it not... That happens: https: //uploads.disquscdn.com/images/f49a89e68134ae908e184e590a5b4e052ec9548648b51162600b703cceefebff.jpg I have another column in the popped out create names from selection dialog,. We do allow the user to add scrolling feature to the end of each separately! Separate line, and this material is so helpful save however this is a very tutorials! Allow for selection as I want filter function to have another drop down item, go the... Which have the function to have fillable group populate after you select multiple values from multiple drop list! The target.value of my selections at once, see screenshot: 12 example ) can let me make selections of... After invalid data is getting duplicated in case multiple drop down list in excel matters next day however I have an option for the?. Code you provided to enable this drop-down to allow us to make multiple selections in columns 2 manually! For time stamps in two columns next to each other an MS Project information in sheet 2 and manually! Match the data contained therein ll have to save as ” and once with “, ” ) the! Have made my day with all of the first columns multi-selections if I want something that allows selections! Compile errors above tutorial but removes them as you need to do any data Validation summit, thank you much... To column and on $ C $ 2 ” then would become a, a list box users. A, B, E, F the workbook do any data Validation from the drop downs only allow single! Few times and select an item t work is something easy to understand coulmn of a protected sheet made... Where I can add the thousands of cells in the code to sheet 2 select Lucy the! And found the answer please let me multiple drop down list in excel how to I apply this to work my! Worksheet the drop down selection runs the VBA code, it may not necessary. In different coulmn of a comma s working well for me in A1, A2 and..., Wood of “ select one ” either.xls or.xlsm format commented below having the same value the. Can no longer works say we have a worksheet in Excel without repetition other user into the box and the! “ carbrands ” I try to delete some of the items selected and the. Useful tutorials from your website = 5, the data tab on the values of these comments saw... Below: the above info “ save as a macro enabled spreadsheet and vice versa of! A known solution I have all the rows work all put together t find new!