WATCH NEXT - Be sure to watch this video next to see how to hide your extra "list" tabs from your form: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ha4LxCPGdPc.html
by far the best and easy to follow code and explanation, after 7 videos from others, either something is missing or not explained well enough in simple English. Great work and thank you.
Thank you so much this is great info. !! just to let others know if you want extend this feature to all rows of column or to a large no. of rows of the column then use Target.Column = 3 And (Target.Row >= 1 AND Target.Row
Finally, instructions for a mac operating system! Thank you so much, I managed to create my list with your step-by-step instructions and applied the rule to a whole column, which I found after scrolling down the comments, where someone very helpfully provides an additional instruction.
I watched another video of a guy explaining this and I don't know what he was talking about. You are fantastic at what you do. Thank you so much for this.
Perfect! This worked perfectly for my drop-down list and I was able to edit it to include even more cells. You made me look like a hero today! (And I said I got the code from a RU-vidr - my bosses were just happy it worked!) Thanks again!
By far the best explanation I've seen on this subject, easy step by step instructions to follow. And you are also very pleasant to listen to and watch :)
Thanks for this Sharon. I had a student ask me how to do this today and I could help her right away. Have subscribed to your channel now and will follow your new content.
Thank you so much for taking us through the steps in the most basic way. I went though many videos and I was just not winning, until I watched this. A quick question, how do i drag the VBA formula so that is applies to the next cells for example and not just 1 line. Thanks
Very useful thank you Sharon! Let's say I want this to apply this code to a range of cells in a column. What would I use as the target address? Surely there must be a simpler way than choosing to Or each Target.Address
Thank you Malcolm, for leaving a comment. I'm glad you found the video helpful. Thank you for taking time to leave a comment. I really appreciate your supporting my channel!
Hi Sharon, your instructions are really clear. It work out great for me. I was wondering how I could modify the code to have this feature on a full column and not target cells ? For example cells E1 to E250. Thank you for you help !
You can if you change the row in the VBA that says "if target.address..." with; "If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 13 Then". The numbers refer to the column number, i.e. A = 1, B = 2 and so on.
The content was great. Thank you. And you standing on the side of the video os a cool feature, so it’s not only a voice but an actual person, but it was distracted a little, as you are pretty cute. Lol
Thank you for a very concise and great video. You ensure to include all the essential info (e.g. save the file as macro for instance), so that this becomes easy to replicate. Also thank you for the code in the text here on RU-vid. I have tried other videos on this and just could not get this to work on my own sheets - so Thanks for a great video. It worked now !! ;)
This is very helpful. Quick question, how can I apply this to the whole column? Is there a code to apply to the column or it has to be done manually using Or Target.Address =...? I want to apply this from row 3 to 195. Please let me know.
Instead of "If Target.Address = "$A$10" Or Target.Address = "$D$10" Then" how can you make this a range? For example, "A$10:$A:$20"... I know it's not that simple
Great videos, This was so absolutely helpful. I created a code for multiple selections in drop list, separate lines, my question is how to deselect ( with Strikethrough )items were selected for tracking issue, Thanks
So helpful! I didn't know you could add them on a separate line within the cell. Thank you! How would we apply this to several cells within the same column (i.e. G5 to G13)? And how do we edit/add to the code to make an item, if clicked again, disappear?
@@stoenixlikephoenix4551 Change this line to the range of cells like this: If Not Intersect(Target, Range("G3:G100, H3:H100")) Is Nothing Then This ensures that the Column G and H from cell 3 to cell 100 has the same drop-down list
Thank you so much for this tutorial! Wondering if there is a trick to deselecting something once it has been selected? Or, if the only option is to delete the cell and re-add the list items you actually need?
Hello! Unfortunately there is no easy way to selectively "de-select" items from the list. You would have to clear the cell and re-select desired choices. Hoping Excel will make some enhancements to this in the future. Thanks for watching my videos!
Thank you! The only issue; I have a column of about 50 rows where they all need multiple validated data. Is there a way to have an entire column of say 60 lines items be able to do that?
This was great video thank you. I'm actually trying to make full columns multi select drop down columns E - G does anyone have the correct code for allowed multi select for full columns?
I don't know if you'll get this comment. Your video was extremely helpful (better than any others I watched on the subject!) and I was able to create 5 columns with multi-select drop down lists. My question is how to adjust the code (if possible) to have the same list show up in each row within a column?
Hello! Please try this code (the "Set rng..." line is modified to include all cells in column A through E, as an example): Private Sub Worksheet_Change(ByVal Target As Range) Dim Oldvalue As String Dim Newvalue As String Dim rng As Range Dim cell As Range Application.EnableEvents = True On Error GoTo Exitsub ' Specify the columns to apply the multi-select drop-down list (A to E) Set rng = Intersect(Columns("A:E"), Target) If Not rng Is Nothing Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else If Target.Value = "" Then GoTo Exitsub 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 & vbNewLine & Newvalue Else Target.Value = Oldvalue End If End If End If End If Exitsub: Application.EnableEvents = True End Sub I hope this helps! Thanks for watching my videos!
@@SharonSmithThank you! I found another video that had me edit to read " If Target.Column = 3 Or Target.Column = 5 , etc" Now my problem is that I want to edit the text in a header that isn't part of the drop down, but because its the whole column, it won't let me edit the words. If you know how to target specific cells within a column, that'd be great. Meanwhile, I'll keep searching!! Thanks so much!
Thank you so much. This was really helpful, Sharon! But could you please help me with the code for deselecting the option from multiselect dropdown? Something which can be included in the above code?
Excellent video, but how do I repeat this for the next 300 cells? it worked with one but can you explain what code to use for working for a series of cells for example from 1-300. Thank you. It is not handy to have to create 300 lines, there must be a code for that, thank you again!
Glad to see you found the answer. Yes, you can modify the code as needed. Thanks so much for watching and finding the answer you were looking for. Take care!
Hi Sharon, it works great for me. Thank you. I need to create multiple selection drop down list but here there are checkpoints : 1. This drop down list should be editable. Meaning if i have created colour drop-down list and in the same cell i have selected 4 colours Red, Black,Blue,Cream. I want to replace Black>Yellow in this case how to replace this value? by using VBA codes or list box. 2. In this drop down list i need to insert duplicate colours. Meaning if i have selected Red,Black,Blue,Green,Red here in this case i have selected 'Red' twice. In this case how you will replace Red colour? You can suggest some functionalities that can meet this requirements. I hope this is one of the challenging task i have put infront of you. Hope you will crack it. Waiting for your reply.
Hi Sharon - this video was so helpful. I have a list of names and for that list of names I want the drop down multi select for each person. When I tried to do this it only gave the multi selection for one person. Do you have a video that shows how to do this for multi lines ?
Hello. Building on the VBA code example from this video, to create a multi-select drop-down list for each person in Excel using VBA, you can modify the existing code to handle multiple cells and their respective drop-down lists. Here's how you can adapt the code: Private Sub Worksheet_Change(ByVal Target As Range) Dim Oldvalue As String Dim Newvalue As String Dim cell As Range Application.EnableEvents = True On Error GoTo Exitsub ' Check if the changed cell is within the specified range If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Or Not Intersect(Target, Me.Range("D1:D100")) Is Nothing Then Application.EnableEvents = False For Each cell In Target ' Check if the cell has validation If Not cell.Validation Is Nothing Then ' Check if the cell is not empty If cell.Value "" Then Newvalue = cell.Value Application.Undo Oldvalue = cell.Value If Oldvalue = "" Then cell.Value = Newvalue Else ' Check if the new value is already in the cell If InStr(1, Oldvalue, Newvalue) = 0 Then ' Append the new value with a line break cell.Value = Oldvalue & vbNewLine & Newvalue Else ' Restore the old value if the new value already exists cell.Value = Oldvalue End If End If End If End If Next cell End If Exitsub: Application.EnableEvents = True End Sub In this modified version of the code, the code checks if any cell within the ranges A1:A100 or D1:D100 has been changed. It iterates through each changed cell and applies the multi-select functionality individually. It ensures that the multi-select functionality works for each cell independently. Make sure to adjust the range "A1:A100" and "D1:D100" to fit the range where you want the multi-select functionality to be applied. This modification should enable multi-select functionality for each person listed in your Excel sheet. I hope this helps you out. Best of luck! Thanks for watching my videos!
Thank you so much, Very helpful, can you please tell me what should I write in the code if I need a comma instead of a newline? and as well is there a chance to have a multi-selection drop list for a full table column so instead of If Target.Address = "$A$10" Or Target.Address = "$D$10" Then I do range from "A1" TO "A50"
Hello, This video was very helpful. As you did, whenever I select multiple selection from drop down list, it is getting displayed in next line, I was wondering if there is an option available that can get the multi selection displayed in adjacent cell of next column ? My purpose is to apply filter to count nos. of different selections. If this can be possible I will be able to select from one single drop down (containing 5 selection items) and later apply filter on each column to get count of each selection item.
In the VBA code, where it says "If Target.Address" change to "If Target.Column". Then after the "=" where the cell name is put the column number not letter. (i.e. column C would be the number 3) That would make that entire line look like "If Target.Column = "3" Then" if I was trying to apply it to column C. Now, I did have all the cells I wanted to modify this way selected/highlighted before I clicked on "Visual Basic" under the "Developer" tab. That does seem to matter. There may be a better way, but this worked for me.
@@brandonhill8766 Hi Brandon, this is really helpful. but it doesn't work on several columns. Like I have at least 5 columns that I need them to allow multiselect and when I try this code it doesn't work. It works for maximum 2 columns. Any suggestions?
Hello, Congratulations, your videos are the best! Please help me with this... Hoy can I include a "Select all" option in a drop down list?, because once an option is selected I can't look all the options again.
Hi Sharon, I'm curious - how would you suggest modifying the VBA code to apply to an entire column and not just two single cells as you did in this video? Thank you so much for your clear videos!!
You can if you change the row in the VBA that says "if target.address..." with; "If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 13 Then". The numbers refer to the column number, i.e. A = 1, B = 2 and so on.
This was so helpful. Thank you for posting, Sharon? If I wanted to duplicate the same dropdown list within an entire column, how would I adjust the VBA code?
Hi Sharon, great video and very helpful! 👍 I have a sheet with various columns I need dropdown menus for. Some cells require a dropdown with single values and some need multiple choice answers. Is there a way to adapt the VBA code to allow for single and multi-select dropdowns in the same sheet? Thanks!
I just tried this and it works - about two thirds down the code: Target.Value = Oldvalue & ", " & Newvalue Recommend that you put a space after the comma so it automatically adds for you.
The video seems to be very self explanatory yet, I have managed not to achieve the same result. Mind you I only had one DV cell so I thought it would be a case of If Target.Address = "$D$16" * my cell but didn't work :-( I am sure it is me, but that does not take away the fact that you did do a good job explaining. Thank you
This is great. How can I program to remove item from the cell once I don't need it there? Ex. I gave the id badge back to the employee therefore I wouldn't have it on this list?
GREAT video - thank you so much. One question and my apologies if someone already asked. If I selected two values and one of them wasn't the one that I wanted to select, how do I remove that value? Currently, it seems that I have to delete all cell values and reselect everything. Any thoughts?
Sharon, Thank you for this wonderful video. I was able to use your code and it worked well. I do have a question, I have a form that has multiple rows which these drop down menus will be used. Is there a way to expand the code so the cells in a specific column have these drop down menus?
Tysm for this video! Is there a quick way to have the vba code copied to a new sheet? I'm using a form with this vba code within a sheet as a template that will be duplicated on multiple other sheets and have found when I c&p, the vba code does not come with.
Thank you so much this video. It has been very helpful. I was able to use your code for an entire column of data. However, I have two other columns that need to include multiple drop-down selections in each cell. How do I modify the code to do this? Thank you.
Great video on the topic! Two questions. 1) Is there a line of code that can be added to restrict the number of selections? Like, lets say I have a list that I only want the user's top 3 from the list of dozen plus options. Can that be done where the forth selection won't be able to be made because you restrict them to 3 (or x ) selections? 2) Is there someway the number of selected from that list can be counted or tallied in another cell? Thanks for your excellent videos on forms!
This has been very helpful, I was not able to find an answer to the additional questions. Q: I have applied this to a column, this works, but I want to then be able to filter based on the chosen outcomes, the filter reads each cell as one body of text, are we able to separate the outcomes to allow for filters to work?
Hi! Thank you for making this video. I am wondering if this can be modified to make a dropdown checklist instead of a checklist box? I am organizing my wedding guest list and want to make sure we are accommodating for multiple disabilities, and any language barriers for 100 people. I've been having trouble trying to make it into a dropdown checklist for each person. Any ideas? Thank you for all of your help! :)
This was an awesome video, but I had an additional question: How do you de-select an item (i.e. remove it from the list) after the list has been populated?
If you mean removing things from the original list, you can either edit your original list and remove the item or you can change the range that your data validation identified when you created the original list. if you mean change the results from the multi-select you chose, I clicked away from the created selections and clicked back to the list and hit "backspace". While I couldn't remove a single line, I could remove everything and start over.
Hi there! Great video! It's working perfectly, but when I try to remove one item I get an error. Im using this tool as a task organizer like: name, address, phone. Is there a way to remove an item from the list. Like once I get the name Id like to take it off of the list. When I try and backspace or "delete" the item I get an error code.
Thanks Sharon - very useful. Could you explain how to apply the vba code Multi-Select Drop-Down List in Excel to an entire column as oppose to having to manually input each cell reference into the code. I have a worksheet where I want the VBA code to apply to over 100 cells in one column
Hi, If you are looking to use this approach for multiple rows in the same collumn, would you still need to add each cell identifier into the code or is there a way of adding a range?
Hello! Unfortunately Word does not have a multi-select drop-down form field option. I would suggest using check boxes if there aren't too many options and/or if your form can allow that formatting. Otherwise, you may want to create your form in Excel, Microsoft Forms, Google Forms, or another tool that offers the multi-select drop-down. I hope Microsoft will add it to Word in the future, but we'll have to wait and see. Thanks for watching!
Thanks for this video! One question: could you explain the section of the code that reads " Newvalue = Target.Value, Application.Undo, Oldvalue = Target.Value"? Does the Application.Undo remove what was just entered into the target object, which then causes Target.Value to become what was in the target cell previously?
Great videos! You have a video where you explain how to auto populate text from a drop-down list and a video where you explain how to create a multiple selection list. But how do you combine them? Can you create a multiple selection drop down list and populate your sheet with the text from the selected options in that list?
Thanks for this info, but how to apply this to all row? like for example i wanted to have a multiple drop downlist in all rows of column A? Is there any specific code for that? If I follow (for example) the "or Target.Address = "$A$2 and so on until row 200.. " its too much code... hope you can help me.. thanks!
Ive done this but I also need to protect parts of my sheet and when I enable the protection (only other cells are locked, not cells with the drop down) the multi selection stops working. Is there a way around this?
very good explaining and it work with me. I change few things to work with a range on two columns and its work with me. Thanks a lot Sharon. for columns range A3:A500 and B3:B500 Here below VBA code: ------------------------------------------- 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 = 1 And (Target.Row >= 3 And Target.Row = 3 And Target.Row
Hello Sharon! Thank you for making this video. This video was very helpful. I want to create a material lists and their quantities. But when i use the same quantity for different materials, I've been having trouble trying to update the cell with the same quantity. For example: I have "samsung" and "Iphone" mobiles as a material lists on my 1st cell (A2). I want to apply "1pc" as a quantity for both materials on the 2ns cell (E2). samsung 1pc Iphone 1pc any VBA code which might fix this issue? Thanks !!
Hi Sharon, I'm having trouble getting this script to work when I protect other parts of the workbook. Can you tell me how to keep Protection from disabling the script? Thanks!
Thanks for your guidance on creating a multi-select drop-down list. However, I need to program more than 35 cells on the same sheet. The process you provided worked for 18 cells, or none at all if I added a script that includes 36 cells. Please help.
Hi Sharon thank you you very much for the video it is very creative and useful I have a question, is this drop list analyzable, can we implement a search or a vlookup to it?
Love this code. Was using it to fill in comments that are typed a lot in certain categories. However i noticed, if any physical typing is done in addition to the auto comments, The code will produce a duplicate of that comment. So i will have two comments. One with the original auto comment, and one with the auto comment PLUS what i typed in additional to the comment. Im not sure how to fix this in the code itself.
Thanks. I have done all the steps. But when I save the code and hit the Run, it asks me to assign a macro name (a small window pops up). and whether I hit the run button or just like you did, close the View Code , when I go to excel and try to see if multiple names cane be selected, I see that still the names get overwritten.
Hello, It sounds like there might be an issue with how the macro is assigned or executed. Here's a step-by-step guide to ensure everything is set up correctly: Insert the Multi-Select Drop-Down List: Make sure you have a drop-down list in your Excel sheet where you want users to be able to select multiple items. Open the Visual Basic for Applications (VBA) Editor: Press Alt + F11 to open the VBA editor. Insert a New Module: Right-click on the project explorer pane (usually on the left side of the VBA editor), select "Insert" > "Module." Paste the Macro Code: Copy the VBA code for creating a multi-select drop-down list into the module you just inserted. Close the VBA Editor: Close the VBA editor by clicking the close button or pressing Alt + Q. Assign the Macro to the Drop-Down List: Right-click on the drop-down list where you want to enable multi-select, select "Assign Macro," then choose the macro you just created. Test the Multi-Select Drop-Down: Return to Excel, click on the drop-down list, and check if you can select multiple items without overwriting. If Excel is still asking you to assign a macro name when you run the code, it could mean that the macro wasn't properly saved or there was an error in the code. Make sure to save the VBA project after pasting the code into the module. If the issue persists, try restarting Excel and reassigning the macro to the drop-down list. Additionally, double-check the code for any syntax errors or missing components. I hope this helps your issue! Thanks for watching!
@@SharonSmith After I've created a module and pasted the code in it, I saved it and closed the view code. But when I go to excel and right click on the drop down list, there is not such thing as Assign Macro. Also you say 'then choose the macro you just created'. Which macro? you mean Module?
While selecting the options from the dropdown list, I accidently selected one entry which shouldn't be there. But now, I'm unable to delete it without removing all the other selections with it. What to do in such a case?
Ok this worked great until I saved and closed the document. I worked on half of the row where I needed the multi-select values selected. I saved the file as Excel Macro-Enabled Workbook, but when I reopened the file this morning it will no longer let me multi-select. Note: The VBA code is still there.
