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?
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.
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
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! 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?
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 TH-camr - my bosses were just happy it worked!) Thanks again!
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
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
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.
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.
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 :)
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!
Not sure if you still need to know how to deselect picks, but here is what worked for me...Right below the last Else statement and directly above Target.Value = Oldvalue insert the following lines: Oldvalue = Replace(Oldvalue, Delimiter & Newvalue, "") Oldvalue = Replace(Oldvalue, Newvalue & Delimiter, "") Oldvalue = Replace(Oldvalue, Newvalue, "") Change the "Delimiter" to whatever delimiter(s) you used. Hope this helps!
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?
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!
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.
Hello, can you please help me urgently? At minute 4:35 you have inputted two cells only what If I want to do the same for hundred of cells. how do I format the VBA code' for example if I want to input range C15 to Z15 how would I do so?
To modify the VBA code to monitor changes in the range C15 to Z15 instead of just specific cells, you can adjust the condition in the Worksheet_Change event handler. Here's the modified code: Private Sub Worksheet_Change(ByVal Target As Range) Dim Oldvalue As String Dim Newvalue As String Dim rng As Range ' Define the range C15 to Z15 Set rng = Me.Range("C15:Z15") Application.EnableEvents = True On Error GoTo Exitsub ' Check if the changed range intersects with the specified range If Not Intersect(Target, rng) 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 & vbNewLine & Newvalue Else Target.Value = Oldvalue End If End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub In this modified code: 1. I added a Set rng = Me.Range("C15:Z15") line to define the range C15 to Z15. 2. I replaced the condition If Target.Address = "$A$10" Or Target.Address = "$D$10" Then with If Not Intersect(Target, rng) Is Nothing Then to check if the changed range intersects with the specified range C15 to Z15. If it does, the code proceeds to handle the change. Otherwise, it exits the subroutine. I hope this works for you. Thanks for watching!
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.
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
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, 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"
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.
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?
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?
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?
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.
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?
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?
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?
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!
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!
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 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?
Hi Jan! Great question! On about the sixth line of code that says: If Target.Address = "$A$10" Or Target.Address = "$D$10" Then Replace with: If Target.Column = [insert number of column] Then by number of column, I mean count over and use a number instead of the letter, so for example column A = 1, B = 2, C=3, etc. then all cells in that column will have the code applied. Hope this helps! Thanks for watching!
@@SharonSmith Hi Sharon great information than you very much, I have tried so many advice from various expert yours is the greatest one. I have one question to ask; I have added multiple drop box menu for my sheet so used If Target.Column = [insert number of column] or [insert number of column] Then the moment I have added second column my excell functions gone made, even adding to different cells is not proparly working. If I used it for one column than everything goes back to normal... How can I fix the isseu? I need multiple drop box menu like this and other functions of the excell as well. thank you very much in advance.... Also if anyone has suggestion I am all ears : )
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 TH-cam. 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 !! ;)
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!
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!
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.
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?
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
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.
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.
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!
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!
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.
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?
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. 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?
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.
Very interesting video, one question: If I want to add another different multi-select drop-down list into the same worksheet, how can I do it? The problem is how to both manage them since Private Sub Worksheet_Change(ByVal Target As Range) can handle one event.
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 !!
WATCH NEXT - Be sure to watch this video next to see how to hide your extra "list" tabs from your form: th-cam.com/video/ha4LxCPGdPc/w-d-xo.html
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?
Thanks a lot for such a great video. However, is there a way to deselect a row previously selected?
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.
Glad you found what you needed! Thanks for watching!
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.
Oh, thank you so much! That is a great compliment. I'm glad you found my videos helpful. Thanks for watching my Channel! Take care!
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.
So glad to hear you found my video helpful! Thanks for taking time to leave a nice comment. I appreciate it very much. Thanks for watching!
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
That's exactly what I needed. Thank you!
@@grzewicz1109 wc ! I am glad it's useful :-)
Thanks for sharing this tip!
Great tip, just saved my life :)
How would you apply it to multiple columns? For example ın addition to Column 3 also Column 4-5-6?
This was the best explanation I’ve seen after researching about 5 or six videos on how to do this. Thank you!!
Thank you so much, Carol! That is a nice compliment. I'm so glad you found my videos helpful. Thanks for watching!
Same!!! Thanks so much!
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
same question
at 5:45 you mention changing the line to show commas and have entries on the same row. What does the code need to be for that? Thank you
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?
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 TH-camr - my bosses were just happy it worked!) Thanks again!
Hi Patty, that is awesome! So glad my video helped you out. Thanks for watching and taking time to leave a nice comment! Best of luck to you!
Thank you for creating this easy-to-use step-by-step video. I really appreciate your ability to articulate this process.
I'm so glad you found it helpful! Thanks for subscribing to my Channel and for watching my videos!
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
Wanted entire column also. Thanks for the content
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
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.
This was my question, and I did exactly what you said, and it worked. I am so happy! Thank you!@@GM-js6jn
Thank you so much. 🙌@@GM-js6jn
Does Column A need to be written in quotes? For example, If Target.Column = "1" @@GM-js6jn
Well explained and thank you Sharon. I have however an issue and still not able to multi select the inputs as described. What could be the issue !?
Great tutorial. it works great. However how do I deselect an item already picked from the list once it has been selected?
Hi, any idea how to deselect once selected?
Hello from France, you saved my day ! Your explanations are crystal clear !
Thank you very much, Bruno! Glad my video saved you day! Thanks for watching!
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.
Select target as a column such as B:B or A:A
Thank you so much for this video. Someone asked my question, and it was answered, and I was able to do what I set out to do. Thank you!
That is great to hear! Glad you found it helpful! Thanks for watching!
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 :)
Thank you, Sohail! That is very nice of you. I’m glad you like my videos!
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!
Not sure if you still need to know how to deselect picks, but here is what worked for me...Right below the last Else statement and directly above Target.Value = Oldvalue insert the following lines:
Oldvalue = Replace(Oldvalue, Delimiter & Newvalue, "")
Oldvalue = Replace(Oldvalue, Newvalue & Delimiter, "")
Oldvalue = Replace(Oldvalue, Newvalue, "")
Change the "Delimiter" to whatever delimiter(s) you used. Hope this helps!
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?
immense pleasure to join your channel, the way you explaining its awesome .
Thank you so much for subscribing to my channel and watching my videos! I'm glad you find my videos helpful!
@@SharonSmith how can I apply or drag down this code for apply several columns
Thanks so much!! this worked perfectly and you explaied it so clearly
So glad to hear that! Thanks for watching!
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!
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.
That is great, Thomas! So glad to help. Thank you for subscribing to my channel and watching my videos. I appreciate it!
I was wondering what we can do to have the Macro applied to a range of cells?
That's exactly what I was wondering too
Hello, can you please help me urgently? At minute 4:35 you have inputted two cells only what If I want to do the same for hundred of cells. how do I format the VBA code' for example if I want to input range C15 to Z15 how would I do so?
To modify the VBA code to monitor changes in the range C15 to Z15 instead of just specific cells, you can adjust the condition in the Worksheet_Change event handler. Here's the modified code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Dim rng As Range
' Define the range C15 to Z15
Set rng = Me.Range("C15:Z15")
Application.EnableEvents = True
On Error GoTo Exitsub
' Check if the changed range intersects with the specified range
If Not Intersect(Target, rng) 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 & vbNewLine & Newvalue
Else
Target.Value = Oldvalue
End If
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
In this modified code:
1. I added a Set rng = Me.Range("C15:Z15") line to define the range C15 to Z15.
2. I replaced the condition If Target.Address = "$A$10" Or Target.Address = "$D$10" Then with If Not Intersect(Target, rng) Is Nothing Then to check if the changed range intersects with the specified range C15 to Z15. If it does, the code proceeds to handle the change. Otherwise, it exits the subroutine.
I hope this works for you. Thanks for watching!
@@SharonSmith you are an angel. I will try this and get back to you.
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.
Thanks Sharon for creating this video. It was very helpful.
Glad you found it helpful! Thanks for watching!
is there a way to deselect a row previously selected. I am using rows instead of commas?
hi great video, is there a way to create a different vba code for each column?
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
Great instructions. Followed and 1 time through and it worked.
That is great news, Jill! I'm so glad you found it helpful. Thanks for watching and for taking time to leave a comment. I appreciate it!
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?
I second these questions, if anybody knows. Thanks!
Would like to know the answer to this question too! Otherwise, super helpful!!
@@shampoonka I hit clear contents for that cell and started over although I wish i could uncheck it!
@@brittanyingold807 for sure that would be helpful to be able to unselect!
@@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
Thanks!
Thank you so much for the Super Thanks donation to my Channel! I appreciate it so much and am so glad you found this video helpful. Best of luck!
Hi! Thank you so much for the video! It really helped me! I wanted to know how to undo it after i select multiple ones by accidnet?
Did you find the answer ?
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"
Exactly my questions! Would love to hear answers! Ty so much for this video!
Target.Value = Oldvalue & ", " & Newvalue
Thanks Sharon, It doesn't seem to work when the sheet is locked. Is there anything around this.
Awesome tutorial. How can I have the selections come up with a comma spacing them next to each other?
You would replace the line of code where it says 'Target.Value = Oldvalue & vbNewLine & Newvalue' with 'Target.Value = Oldvalue & "," & Newvalue'
Hi, this was very helpful. What would the code need to change to if I wanted the VBA code to target specific columns?
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.
Great content. Quick question - Does anyone know how to de-select a name from the drop-down list?
What is you need it on a whole column? Do you have to add each for of that colum?
Thanks exactly what was i was looking for, very hard to find on youtube
Glad you found it helpful! Thanks for watching!
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?
What I I want to apply this code to a whole column? not just select single cells? Also, how to add a comma between the different selection options?
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?
Well, it worked when I highlighted all of my 5 columns before clicking on the VBA tab!
Brandon, you're a life saver
I'm not get it😢
Thank you, this was so helpful
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?
Thank you , very helpful!
I’m so glad you found it helpful! Thanks for watching!
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.
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?
Thank you for this video. What I am looking for is to do the same but for all the cells of a column. How do to do? Excuse my english.
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?
Hey, did you find a way to do this? would highly appreciate your help if you have!
Thanks for posting this. It was very helpful.
Glad you found it helpful! Thanks for watching!
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?
same que form me as well, let me know if you found the answer it would be really helpful.
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, as always Sharon, very clear instructions and love your website. Avid follower 👍
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!
This was very helpful, thank you. I have a question though. How would I write this for a big range of cells, eg multi selection for range A1:A900?
I applied to the entire column. change 6 to :If Target.Address Like "*L*" Then
where "L" was my column
Thank you. Can this be done as a single checklist instead of repeated opening of the list?
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?
use this line
If Not Intersect(Target, Range("G3:G100, H3:H100")) Is Nothing Then
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?
Hey thanks it work nicely..
But after protect the sheet it not working...
Please help me on this.
Great video, how do you apply the macro to all rows in that column? Not just 1 cell? Thanks
Hi Jan! Great question! On about the sixth line of code that says:
If Target.Address = "$A$10" Or Target.Address = "$D$10" Then
Replace with:
If Target.Column = [insert number of column] Then
by number of column, I mean count over and use a number instead of the letter, so for example column A = 1, B = 2, C=3, etc. then all cells in that column will have the code applied. Hope this helps! Thanks for watching!
@@SharonSmith many thanks Sharon, what if I wanted to start on row 2 of the columns to allow for column titles? 🤔
@@SharonSmith Hi Sharon great information than you very much, I have tried so many advice from various expert yours is the greatest one. I have one question to ask; I have added multiple drop box menu for my sheet so used If Target.Column = [insert number of column] or [insert number of column] Then the moment I have added second column my excell functions gone made, even adding to different cells is not proparly working. If I used it for one column than everything goes back to normal...
How can I fix the isseu? I need multiple drop box menu like this and other functions of the excell as well.
thank you very much in advance....
Also if anyone has suggestion I am all ears : )
Can you adjust the VB for a range of cells
Hi thank you for this video. I do not have "Developer" tab. How, do I get it? or make it visible on my excel?
Go to File-Options-Customize Ribbon. The developer checkbox is under Main tabs. Click it and it will become available on the ribbon
Hi Silas, here is a video on how to enable the Developer tab: th-cam.com/video/zKnMvK2YZ1A/w-d-xo.html Hope this helps! Thanks for watching!
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 TH-cam.
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 !! ;)
Glad you found it helpful! Thanks for watching!
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!
Did you find out how to do it?
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!
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.
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?
The great difficulty about this is that, what do you do when the pivot table does not recognise these multiple values as separate values?
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
Did you got the way? M looking for same solution..
Target.Column = 3 And (Target.Row >= 1 AND Target.Row
Worked with ease!@@shamaragough7451
Sharon, this was very helpful! Thank you! Quick question, what is the code for displaying it on the same line using commas instead of new line?
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.
Hi Sharon. What if I have 1000 cells in a column that I need to apply this to?
You actually answered it below some comments my question!, thank you it works! :)
Glad you found the solution in the comments and that everything worked for you. Thanks for watching!
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.
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!
Hi Sharon, is there any ways we could analyse these multiple selections as separate values using pivot table?
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!
What code do I use if I want to make a whole column multi select possible?
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.
Hi! How did you apply this to an entire column?
it is possible to do this in Word with a drop box?
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?
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. 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?
Such a great instructor, always keeps me engaged ❤
Thank you, glad to help!!
Any tips to apply this formula to an entire column without having to individually type out each cell?
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! :)
Can i somehow make it comply in browser/google sheet view?
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.
@@yvettelee3990 Thank you!
any tips on how to translate to google sheets?
Does this multi select follow "OR" logic or "AND" logic?
SO helpful; thank you!
So glad you found it helpful, Katie! Thanks for watching!
Very interesting video, one question: If I want to add another different multi-select drop-down list into the same worksheet, how can I do it? The problem is how to both manage them since Private Sub Worksheet_Change(ByVal Target As Range) can handle one event.
did you find out? I have the same problem here.
Hi! how do i do this for multiple cells in one sheet?
Hi, if you wanted to apply this code but only to a specific Table what would the coding be instead of sheet or column? Thanks
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 !!