You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.
Single Selection Drop Down
By default, data validation drop downs only allow you to select one item.
In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.
Select Multiple Items From the Drop Down List
Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
You can display all the selected items across a row, or down a column, or keep them in the same cell.
Keep reading, to see some examples of this technique, and how it works.
There is also a video that shows the multiple selection drop down list. To try it for yourself, follow the download link f
Watch the Data Validation Video
To see these techniques, and a few other multiple selection examples, you can watch this short video.
_________________
I’m using your samecell multiple selections VBA code and I’m trying to apply it to drop down lists throughout a spreadsheet. I’ve been able to use the CASE statements to get most working but it occurred to me I have more drop downs selected than excluded. For example, I have many lists where multiple selections should be allowed but not drop downs like “male/female” or “yes/no”. Is there a way to let the code apply to the entire sheet and simply exclude the few lists you don’t want it to apply to?
The code is fantastic but I’m just trying to tweak it to accommodate my needs a little better. Thanks.
@Michael, you could put the columns that you want to exclude in one Case statement, and the multi-selection code in the Case Else section. For example,
Select Case Target.Column
Case 3, 4, 6
‘do nothing
Case Else
‘multi-selection code goes here
End Select
Debra it working now. Thank you very much for your support.
@Raheel, great, thanks for letting me know that it’s working.
I haven’t tried your fix yet but I will. It certainly looks like it will help us out. In the meantime, I’ve stumbled across another issue. While trying to paste values between two merged cells I got the typical error about merged cells. That’s one problem (any ideas). My fix was to go ahead and unmerge all my cells (my brute force and Infantry method) which has resulted in my columns being narrower than I had hoped. Now my drop downs are so narrow you can barely see the selections. I can’t increase the column widths due to screen width, print width, and display concerns. I’ve seen on this site a method of temporariy increasing pull down widths but I was hoping you could show me how to incorporate that method into the samecell multi selection code. I wasn’t sure if you could have two VBA sections applicable to the same pull downs.
Hello,
There is some great info on here. I want to make a same cell line break with the addremove feature as well. Could some one please provide me with the code, if it is possible. If not just the same cell line break would be enough. Thanks.
Hi Debra,
Many thanks for the videos and your tutorials as they are extremely helpful. I am trying to adapting the “same cell edit” for my project. I copied the code as is in my book as well as the box for “edit entries” and the admin notes but I end up getting an error message which I debug:
“Set rngEdit = Worksheets(“AdminNotes”).Range(“EditMode”)” appears highlighted.
Please advise and thanks again!
hb
@hb, make sure to name that EditMode cell as “EditMode” — there are naming instructions here:
http://www.contextures.com/xlNames01.html
Hi Debra,
Great information. Is it possible to alphabetize and/or sort the result order of a same cell (separated by a comma) or same cell line break?
For instance, the result “A, B, D, E, C” is out of order, can the result be sorted to alphebetize within the the same cell result?
@Greg, I don’t have an example that sorts within the cell, but you could find code that sorts within a cell, like this example from J.E. McGimpsey:
https://groups.google.com/d/msg/microsoft.public.excel.newusers/lWSPnkWHAAM/BNM0RsviaSAJ
Then, add that macro name to the worksheet_change event, so it runs after you update a data validation cell.
If your cells contain text, instead of numbers, change the data type from Double to String.