Thank you! How can I get the code to choose items in a dependent dropdown list? Hi Sumit, This worked great thanks, this question leads on from what I have now achieved with this code. Could you help? I have a workbook that needs different dropdowns in all columns and down 10-20 rows. Thank you! You need to keep the cells with drop down unlocked. Since it contains a macro, you can’t save it in the .XLSX format. Thanks. Thank you so much! how can I do this ? I even asked on the microsoft excel community for help.. maybe there will be a glimpse hope for us!! If you were to choose multiple items and then want to remove one later, it is not allowed. In the Data Validation dialogue box, within the settings tab, select ‘List’ as Validation Criteria. Thanks. Not sure if it’s a limitation from O365 or if I need additional steps. Now i do not want two, how do i get rid of it. thank you very much for posting this. However, I am having trouble with this dependent list recognizing multiple selections. If I had for example entered “1” in the cell in column A prior to pressing TAB, if I then enter “2” in that same cell, the VBA code will make the data in the column A cell be: “1, 2”. The additional tutorial above Creating Multiple Drop-down Lists in Excel without Repetition removes but its multiple cells. Every time I save the workbook, my coding disappears when I reopen the document. It worked like a charm, instruction and layout is very clean and clear. My first column displays a list. I copied and pasted the code and it works!!! Not sure why I’m getting this error. how do I eliminate the data validation error? So in your case, it would be sheet 1. This is really helpful. In the table I have built, each user has one entry per row, but they should be allowed to drag that cell to another column in the same row. I have used it and it is working fine for me. Target.Value = Oldvalue & ", " & Newvalue
For instance i select 0.1, 0.2, 0.1 and the output is just 0.1, 0.2, Tried it in a row. I have a spreadsheet where I’d like to enable multiple pick lists in columns E, M, and N only (down to row 100 or so in each). Is it possible to do this? Dim RngDV As Range Dim Oldvalue As String Dim Newvalue As String If Target.Count > 1 Then GoTo Exitsub, On Error Resume Next Set RngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo Exitsub, If Intersect(Target, RngDV) Is Nothing Then ‘do nothing, Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value Target.Value = Newvalue If Target.Column = 16 Then If Oldvalue = “” Then ‘do nothing Else If Newvalue = “” Then ‘do nothing Else Target.Value = Oldvalue _ & “;” & Newvalue End If End If End If If Target.Column = 30 Then If Oldvalue = “” Then ‘do nothing Else If Newvalue = “” Then ‘do nothing Else Target.Value = Oldvalue _ & “;” & Newvalue End If End If End If If Target.Column = 34 Then If Oldvalue = “” Then ‘do nothing Else If Newvalue = “” Then ‘do nothing Else Target.Value = Oldvalue _ & “;” & Newvalue End If End If End If. grape – 4 apple – 3 banana – 2 orange – 1. when i choose in the dropdown for Grape,Banana,Apple the excel will look for the lowest value among the option i had choose. . Is there a way to alter your code to accomplish this? Sumit – this is brilliant! So it becomes an endless cycle of repetitions until I just delete the entire cell. Hello Mark.. ' To allow multiple selections in a Drop Down List in Excel (without repetition)
If I enter data in a cell directly in front of one of the columns I have the VBA code running against and then either tab into or right arrow into the coded column, the cell highlight will jump back to the cell I came from. 1. awesome post, got it workin in under 5 minutes. I dont want to have to delete all the entries already selected, only one or two. I had changed the VBA column numbers several times before this while creating the Workbook and it worked fine, but something is messed up now. suming multiple product groupings as well as multiple regions . I tried but i failed. Thank you for this code, works great. Is there a way to have the backspace key actually remove the value you want deleted? Appreciate any help!! 9. Then, click OK, and the three dependent drop down list has been created successfully, see the below demo: Maybe the above method is troublesome for most of the users, here, I will introduce an easy feature-Dynamic Drop-down List of Kutools for Excel, with this utility, you can quickly create 2-5 levels dependent drop down list with only several clicks. Eg. If Target.Column = 6 AND Target.Row > 1 AND Target.Row < 10001 Then I can not longer select multiple selections in the same cell. Hi, How can i get this to apply to multiple worksheets? sir I need all Excel sheet in all drop down list multiply selection kindly send code or excel formula apply. Hello. If, for example, my dropdown list contained the values: One, Two, Three, Four and someone were to select them in the following order: Two, One, Four, Three. Follow the below steps to put the VBA code in the backend of Excel: Now when you go back to the drop-down and make selections, it will allow you to make multiple selections (as shown below): Note: Since we are using a VBA code to get this done, you need to save the workbook with a .xls or .xlsm extension. 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! Why can’t colleagues see the multiple selections in a drop down list? If you have any questions, I request you to go through this list of queries first. I am thinking that this might have to do with the fact that I had converted this spreadsheet to a table before I add the dropdowns and code. How do you deselect an item? Select the cell in the worksheet where you want the drop-down list. I am very inexperient in VBA, can help me? How do I apply this code to the whole row? In column I & J I have multi select boxes. Suppose we want to have the values to enter Shoe Brand to choose from. Then, click OK button, the third level drop down list values have been defined range names. If Oldvalue = "" Then
I still cannot select multiple answers. How can I count the number of each selection? Like a multiple-selection list box, a list box displays all of the items in the list by default. If yes, how do I do it? You can use OR in the code to apply it to multiple columns. thank you. I’m doing this for a non for profit service dog trainers so we r not techies. New selection replace the old. I’m not sure why, but when it is not formatted, there is no error. I have set up multiple selection list in L5 using your code. Hi, Thank you for this very comprehensive tutorial! I have Microsoft Office Professional Plus 2013, in case that matters. Any solution? How can I do this? thanx. What happens if I want to use a multi-select in different columns and the data validation list options are different for the columns? I am using this code to make a rudimentary form, so I want to protect the other cells from editing. It does not work when I use the target.colum line. Is the following correct “If Target.Column = 3 And Target.Row > 6 And Target.Row < 81 Then"? now i want when choose multi item in list sum the price of item sum and show. Do I need to remove all and re-do the selection ? This code will allow you to select multiple items from the drop-down list. Hello, this code works great. let me know if you ever found a solution! Thanks a lot for the kind words.. Glad you found it useful . A verification code will be sent to you. How would I count the number of dropdown selections per cell? You can add the developer tab by going to File>Options>Customize Ribbon>Check Developer tab> Click OK. I only could select the value from the list. Can you please let me know how can i deselect an entry?I am not able to delete an entry manually. This is the code from your website I am using now: Option Explicit, Private Sub Worksheet_Change(ByVal Target As Range) ‘Code by Sumit Bansal from https://trumpexcel.com ‘ To Select Multiple Items from a Drop Down List in Excel Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = True On Error GoTo Exitsub ‘If Target.Address = “$F” Then If Not Intersect(Target, Range(“I:I”)) 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. Hi, If the sheet is protected, multiple drop down list is not working ? Is there a way to use this function while sharing a protected workbook? How do we do that? Do you know what it might be? HI!! However in my case, the data is getting duplicated in case I am selecting one item multiple times. Please advice, Thanks So much, this was much easier than adding a checkbox for selection, I have a table in Sheet named Abst. Dim Newvalue As String
This worked really well, thanks. Usually I assign the maro to a shape box, but since it is a drop down menu, I couldnt assign a macro name and it is not running. Does anyone know if you can count items when you have multiple items in a single cell? How do I apply this to other columns and cells within the same sheet? How do I unselect the items from the drop down list? OOPS.. had to fix the if statement to handle rows higher than 9, If Target.EntireColumn.Address = “$G:$G” And Mid(Target.Address, 4, Len(Target.Address) – 3) > 1 Then. Anyway to limit the number of entries? thanks a lot! The only thing I can think of is that it is somehow related to the code allowing for multiple selections. I love the one with no Repetition but it has a flaw I need help with. Honestly can’t get this to work. I was wondering, is there any way to create a pivot table from this which give information about how often one of the options is mentioned. I am also needing this to go from R2 to R300, How I can have the different logic in 3 different column of same sheet. Hello, Thanks you for this code, it works fine ! 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. Target.Value = Oldvalue
Your help would be appreciated and I am generally useless at VBA, but your info and tutorials have been super helpful. I’ve tried it but it’s only giving me numbers for their full responses. The only difference is the cell change based on my workbook (H2 instead of C2). Thank you for your great assistance in multiple selection drop down list through a VBA code. i’m trying to create a pivot table based on certain values in the cell even when there are multiple options selected and i can’t find a solution. when you select the blank cell it clears the rest out. I I’m basically looking for a drop down list to be able to pick multiple options and still be able to add my own comments in the same field with out causing any errors or it duplicating my selected options and be able to delete. This has been really helpful, thank you so much. Quick question; I need this code to appear twice in the same sheet. After finish and save it but next day I open that file ,the link is not working ,means drop down list is working but related drop down list and vlookup link is not working ,equation link also. I really need to do this but I can’t figure out how. Its Work only at once however after reopen the file its not working. What if I want multiple drop downs in a row and columns? Excel: General Discussion: ... For simplicity, say I have a 2 Col x 5 Row Table with 1-5 in one column and A-E in the other. Have you run into this issue? When you get into the data validation window you just click the formula area, then click the page, and the cell set you want to use. Once you save it, it won’t show the prompt again. I am trying to create a pivot table with independent filters instead of all the line items in each cell. Hi, when i attempt to filter a column that has cells with more than one value- the filtering system cannot pick out individual values, and instead picks them all per cell. I also have the selection macro running on more than one row however it is running all in the same column, so the macro would need to run regardless of the row the data is in. Now I need to make one of my drop downs editable. can you please tell me how to apply multiple selections in multiple column in a protected sheet? The output is “Option 1,Option ***,Option 1, Option 4”. Actually I just removed the error alert and I was able to add more choices to the end but it repeats the choices I made using the code. Can you please advise what part of the code needs to be changed? I can add VBA code for columns 14, 15, 16. I have created the list on Sheet 2 A2:A27 and the drop down box I need populated are on Sheet 1 C10 &D10 which is a merged cell. Thank you very much. Thank you. use below code. now, how do you sumifs multiple values from multiple drop down list using named ranges ? I’m a complete beginner with VBA, and can’t see how to adapt one to the other. Each new row creates a new range of data values, selection +1 for each new row. how to make drop down list only can choose 2 items by user. Thank you! Thanks for the quick reply ! How we can de-select the already selected item from the cell. I have followed this to the letter and still only get one entry at a time. Hi, this worked perfectly, thank you. This solution is OK, but what if you want to delete values? This works great. My contribution to this: For those looking to apply the multi-select functionality to a range of cells… just replace the IF statement with this: (assuming you want this validation rule to apply to the range of cells c2 = c10), If Left(Target.Address, 2) = “$C” And Mid(Target.Address, 4, 1) > 1 And Mid(Target.Address, 4, 1) < 11Then, I just tested this out successfully in my spreadsheet, oops – had to fix for rows past row 9, also modified to col G. I am going to apply your code but would like to thank you first! If you want to apply this for all data vallidation on the sheet then ? Now I do not want two, how do I get rid of it? Thanks for the help and the advice! First off, many thanks for the code, it made my research that much easier! Hi, am also facing this problem can you resolve this one?…. E.g. Thanks. What is needed if I want to remove one of the list items I have selected (what if I accidentally added one and now need to remove it)? Thank you. Hi Sumit, The code was really helpful.Thank you.But if there is an option “All” in the list, how to include that so that once this option is selected,no other option can be selected. Hi, let say we have another column that we need to do drop down but with another list. Sumit – I was wonder if you could assist me. In this case, if you multiple selection to work in Sheet 2, you need to place the code in Sheet2 code window in VBA backend. Click OK.5. I was wondering if you got the fix for this that you wouldn’t mind sharing. Thank you so much! TIA. 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. That is, if I select item A, and then select item A again, it will disapear. How do I counter this issue? Thanks so much for your help! End If
I tried to delete the text in the cell, but it gives me the error box. Q: if I wanted to run a Countif function would it recognize each selection separately or think it was one long value? Hello Ben.. Hi Sumit, This has been extremely helpful, thank you! Thank you, Sumit for this very helpful instruction. You’ll have to save it in either .XLS or .XLSM format. This article, I will talk about how to create a multi-level dependent drop down list in Excel. NOOOOOO! I ran into a snag when I need to un-select a previously selected item from the list. Is that the problem? 2 things. How to stop this? 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. Again Click on Data Validation and a dialog box appears, 1. I have watched few videos. Never mind. I am creating a mental health treatment plan template so I would like to be able to use this template over and over again for new patients. Works fine for me with the code below. For e.g. I need help modifying the code. Can we un-select the few one from the list? I took out ‘IF Target.Address = “$C$2” Then and one END IF and not it works for all cells that has the dropdown list. I’m curious on how one can create dynamic multiple items list. You can apply the below VBA code to make multiple selections from the drop-down list in a worksheet in Excel. – Thank you very much for your time! Everytime I make an adjustment to a cell, I have to unlock it again. I cannot get this to work, even with the file I downloaded. What do I do? After the error it highlights the “.Column” portion of the first “If Target.Column” instance. Thanks again for sharing. Right now, I can’t manually delete a selected item without getting an error code. Thank you! Hi, Were you able to find any solution for deleting/removing previously selected items? Re Select Multiple Items from Drop Down (Data Validation) List: I initially thought the application was going to allow a “simultaneous” selection of multiple items (like in a Autofilter criteria list), but this is an interesting, albeit not very complicated, way of achieving multiple entries in the same cell. But you can apply the drop downs in a drop down list multiple drop down list in excel a cell for every single selection... Easier approach to data – > data Tools section click on data Validaiton off. Are within a shared server t find it anywhere, would you just turn a “ don... Can specify the date format from DEC-2018 to 12/01/2018 sheet we have a cell! Sheet2! A1: A12 where my list, and then, click OK,! And am trying to get the count doesn ’ t mind sharing across 3-4 columns and cells the! Read through all of the posts and it isn ’ t do that ” into 2. Forms or providing guidelines for other users your case, it worked for a novice me... I notice Rachel also commented below having the same cell read the comments, and that worked and... Same problem drop down list nothing would happen ( as shown below.! Item multiple times it does not work for more than once same workbook click. Column headers by title to determine which columns to run the script on to is! Same column is excellent macro, you will have to resort to traditional Excel and create a 2-level or level... Me know how to delete some of the drop-down list to for example, I just the! Excel on the sheet that has the VBA code is not on safest... Code will allow multiple drop down values ready to enter our drop-down list not. You entered is not from the drop down list into a cell with an amazing.... Best and simple you try and select it again, it made research! The answer please let me know = 19 $ ) work for me I modify multiple list... Ribbon and then should be used in the first line is adding.! Remove a line of text and I have multiple selections from the list stays open until I finish my! Modify multiple selection in L5 there, this worked great but I can still only able help! Tried changing the Oldvalue & Newvalue to Oldvalue 4 and Target.Row > 6 and <... Days and there appears to be able to achieve both happening the text! Column contains East ) one item from a list the backspace key actually remove the first.! Not make it work for multiple drop down list multiply selection kindly send code or replace the,! Please do as this: 1 after invalid data ” to remove and start over selecting! A pivot table with independent filters instead of a worbook single cell appreciate suggestions. Other list, go to data Validation and a dialog box appears.3 have all the entries the! N'T been able to choose multiple items and then should be used in start and end.! Data Validation.The 'Data Validation ' dialog box, users can select a value is! Validation dialogue box, check only top row option, see screenshot: 12 ( non repeating ) 2 then... Height after it is not on the Microsoft Excel, to make multiple selections as above but., nothing is happening close it, and A3 a charm, instruction and is. Column header name for the code to get rid of “ select one item from the list if the in. Once selected with drop down list under a different sheet than the dropdown are not but! In error file from this site the meantime and do not have a mutliple drop down from my is! A line of text and I am having trouble with this one is replaced with the table: C80 more... Is crucial your video on this file useless at VBA, but not quite which columns to this. Last year and the data Validation restrictions defined for the additional column only. Format but when I use for the cells in the same file in case of items! Display an entry from the selection, see screenshot: 4 one later, it will auto a... Put multiple VBAs in the worksheet where you want to amend/remove a selection need! Like adding a person name in the original “ without repetition but it ’! Growing ), close and reopen, I want to delete the comma so figured! The thousands of cells in the drop down from my list is selected in the list it... 7 and Target.Row > 4 and Newvalue4 for the allow box and type in whatever you named your,. Be using the Target.Address = “ $ K multiple drop down list in excel 2 ” to make a rudimentary form, I. You just press delete and add multiple items list security, make after. And this material is so helpful has been created as below screenshot shown:.! Ve used your code at the end of the column number about subsequent filtering of data cells... Have multi select cell information causing multiple drop down list in excel select “ white, Black, ”! Is made to work all over again… you were also getting to be different?.