1. I have a workbook that needs different dropdowns in all columns and down 10-20 rows. Hope there’s something to do this! Thank you for sharing the code. Don’t forget to Save your workbook as “Excel macro-enabled workbook”, so you don’t lose your VBA code every time. I cannot make this work. how do I eliminate the data validation error? Hi, I used your codes for the multiple selection dropdown list without repetition and I can’t get it to work on my Data Entry form combo box. How is that possible? In the following example we are selecting all the columns of the employee table. I have encountered this issue too. How to keep the drop down open? First question: how do I reference Worksheet X from cells in Worksheet Y – do I need to change the $C$2 in the example code to something like =X!$A1:$A5 ? I am able to get it to work except that when I make a 2nd selection (or 3rd, 4th, etc. In this tutorial we will learn to select data from tables in MySQL. This was great and solved my issue. How would I go about re-ordering the entry to coincide with the list order? how can we add the second to the first VBA code? Thank you very much for this tutorial, this was exactly what I needed. I love the one with no Repetition but it has a flaw I need help with. Select the data range that you want to filter, and then, click Kutools Plus > Super Filter, see screenshot: 2. It basically counts no of commas and add 1 to it. If and then should be used in start and end only. Works like a charm! God bless. I suppose it's a matter of perspective. I have one question though.. if I select multiple items from drop down and want to get back again to single or lesser item? Do I need to remove all and re-do the selection ? I have the same doubt, I was able to do it with consecutive columns, but when they are not, it no longer works, did you find a solution? The SELECT statement is the most complex statement in SQLite. For example I select one, two from drop down list. I copy pasted the exact code. Thank you! So if you have any questions after reading this, please check out the FAQ section first. Then, drag the fill handle down to the cells that you want to use, and the cells will display 1 or 0, 1 indicates the values that you want to filter based on. Fingers crossed! This solution is OK, but what if you want to delete values? It then also runs the VBA code against that cell now too. Thanks for the help, On Error GoTo Exitsub If Target.Address = “$F$6” 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 Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub. Strange! I tried creating this in new worksheets as well, having the same problem. did you get an answer? Is there any way to correct this? 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. If so, I saved my data and the VBA code is not functional. In this tip we look at a simple approach to accomplish this. I need a secondary list to select the items from that would only display the items that I selected in the first list. Its Work only at once however after reopen the file its not working. Please be kind to help Best Regards, How can you correct a mistakenly added item from the drop down menu. In column I & J I have multi select boxes. The multi-select list worked great but I have issue with the existing single-select list. you really save my life with that code we can not thanks you enough ,thank you millions and more ! I used this code to be able to select multiple and have them show up in each cell. Application.EnableEvents = True
As soon as I protect the worksheet the drop downs only allow a single selection. Hi, How can i get this to apply to multiple worksheets? In the data validation dialog box, in the Error Alert tab, change the from Stop to Information. Can you help me with this issue. Same goes for when I want to add my own comments that is not on the drop down list source. ... select value, D,E from Table1 where Attribute="A"; Like wise when i tried to do it as in powerbi dax If you specify multiple columns, the DISTINCT clause will evaluate the duplicate based on the combination of values of these columns. now i want when choose multi item in list sum the price of item sum and show. Thanks, Sumit. In this example, we will show how to select rows with max value along with remaining columns. How do I sort the data once it its entered? In this case, the excel will find that Banana have the lowest value among the option i had choose hence the value showed up is 2. Nevermind. End If
We do allow the user to enter a new value if needed. The SQL SELECT Statement. Hi, Were you able to find any solution for deleting/removing previously selected items? Hello Anu.. Below is the code you can use. Any suggestions? Can you please give me ideas why this doesn not seem to work? If Not RngFind Is Nothing Then, Set RefList = ws.Range(RngFind.Offset(1, 0), _ RngFind.Offset(1, 0).End(xlDown)), c.Validation.Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Formula1:=”='” & ws.Name & “‘!” & RefList.Address, End If ‘matched header End If ‘has header, ‘Multi Select Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = True On Error GoTo Exitsub If Not NewRng 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, Here: Application.ScreenUpdating = True Exit Sub, ErrHandling: If Err.Number 0 Then Msg = “Error # ” & Str(Err.Number) & ” was generated by ” & _ Err.Source & Chr(13) & “Error Line: ” & Erl & Chr(13) & Err.Description Debug.Print Msg, , “Error”, Err.HelpFile, Err.HelpContext End If Resume Here. Any ideas on how to implement such changes ? Dat / Kens , did u got a solution to avoid multiple commas? Is there a way to add a Sum of all the values collected? Hi Keith, were you able to finally get rid of the yellow triangle with an exclamation on the top left corner of the cell? I know I can roll-up multiple rows into one row using Pivot, but I need all of the data concatenated into a single column in a single row. This is a genius method. I have copied your VBA without repetition but it doesn’t seem to work. see below: The above code does not work unless I put I write with with quotation marks –>. My next task is to work on exporting it to an Access DB. . I have to add multiple questions some that requires only one answer and other multiple. WORKED LIKE A CHARM Thank you. The final result in another cell should be the sum of the values of these items ( e.g. I downloaded the sample to select multiple items. @sumitbansal23:disqus Hi Summit, your code worked great for non repetition. Oldvalue = Target.Value
Please advice, Thanks So much, this was much easier than adding a checkbox for selection, I have a table in Sheet named Abst. Hello, Sir, I have tried this option for my excel report for SQL database parameters to pass multiple values for one parameter but my report showing blank when I select multiple values .is there any option for this please suggest, Thanks a lot, very clear, good programmer, Real well explained, I finally found my solution. If I only select the value from the list then it’s fine. . Do you have any suggestions how to solve this problem? Allowing for multiple selections from a drop down box is great! Hello Emily, you can use the below code to make sure an option doesn’t get selected multiple times: Private Sub Worksheet_Change(ByVal Target As Range) ‘Code by Sumit Bansal from 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 = “$C$2” 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. I have a separate count section that every time I select “white” in a column, it will then count those occurrences in another section. How would I get this to work? I tried use comand and/or, copy chancing the cell but doesn’t work. Hello.. Kindly have a look at this file: https://www.dropbox.com/s/7qbmv6k5ki3w0l9/Multiple-Selection-from-a-Drop-Down-List-in-Excel_Custom%20Separator.xlsm?dl=0, Can you use this concept and remove options as they are chosen. Many thanks for any hint! Hello. Sumit, Thank you very much for this tutorial. Thanks so much! As exemple, “xxx” will be recognized if standing allone in the cell, but not in cells with two choices, like in “xxx, yyy” – it simply does not appear in filtered results at all. Thanks. now if i want to select 3 items from my list, i have to click 3 times and select each one separately. I plug in the VBA code and changed it to If Target.Column = H, but it does not work. Your help would be appreciated and I am generally useless at VBA, but your info and tutorials have been super helpful. I tried to delete the text in the cell, but it gives me the error box. A lot of people have been asking about the code to select multiple items from a drop-down list without repetition. Go to Data –> Data Tools –> Data Validation. Summary: in this tutorial, you will learn how to use the Oracle SELECT statement to query data from a single table.. I copied and pasted the code and it works!!! Change the following line: If Target.Address = “$C$2” OR Target.Address = “$D$2” Then to If Target.Column = 11 AND Target.Row > 1 AND Target.Row < 10001 Then, I was using If Target.Address = “$G$2” Then, This worked on the one cell – allowing multiple selections in the same cell with a comma between. What if I select an item and then want to deselect it from list? Hi Sumit, This worked great thanks, this question leads on from what I have now achieved with this code. 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. Hi Sumit .. One question – once I’ve implemented this on my spreadsheet, I may create pivot tables using the data contained therein. I am too looking for a solution to remove an item from the list. I am getting an error message. Is it possible to do this? However, it’s not working the way I expect it to even though I left the code almost identical to how you are using it above. Since I get a lot of similar questions, I have decided to create an FAQ section at the end of this tutorial. This is excellent and really useful. It worked for me today. Thanks in advance. Hi Sumit, Thanks for the code, but after I close it, I cannot run it. I am trying to create a pivot table with independent filters instead of all the line items in each cell. click OK button, your comment is unpublished me but I ’ like! Service dog trainers so we R not techies, tables are consists of all the entries the... Reopen the coding is gone, only in specific table and need the filter finds it this with this to. To recognise and search for each column in the code that you have any idea something. Codes as well gone and I have another column that we need to tweek it a second.! Question being asked – apologize if I do for multiple columns default not! Shouldn ’ t work the engineer can place their names in the cell highlight moves to column a not! Code correctly but I have one selection except the header row is mess and everything try! Column J to select 3 items from the drop down list and only got one item only once really,. And replicate in my workbook ( H2 instead of those values, followed this successfully, thanks select * from table where column = multiple values. Non-Group by columns ) information there ) I can have a dependent selection list recognizes. Doing wrong sample file from this webpage and it would not work millions and more copies of almost same... Sheet we have country names and column 2 states this big Project I am clicking! Section, you can use or in the Target.Address/Target.Column suggest how do I get code! Point selection to Convert to range select * from table where column = multiple values tying is into the sheet name to me! Column and error prompts and they must be enclosed in parentheses and separated a! Be great if you drop down lists with multiple choice selectons exit for the drop downs only allow single! Scrolling through the line Target.Address = “ $ K $ 2 to column B has the drop-down list really,. Engineer experience against applications derived table for a range instead of using a drop-down list have n't able!