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.
_________________
hmmm….I ran across a comment that the SpecialCells function would work with only up to 8,192 non-contiguous cells – I’m definitely exceeding that. Could this be the problem? This was on the Microsoft site – it also states that no error message will be returned within VBA code – and that this is per design. thoughts?
Don, you could set the range to special cells in the Target cell instead:
I’ve been messing about with the various range values for a while – I can get intersections if I set them manually – e.g.,
Set MRange = Worksheets(“MyName”).Range(“DropDown1,DropDown2”)
If Not Intersect(Target, MRange) Is Nothing Then
….go through code
however – now I’m getting same very strange effects in the worksheet. For instance, my ActiveX calendar box is double applying dates, and I appear to be looping through the Worksheet_Change code multiple times – it seems that it’s not really taking the range. I’ve seen a related entry at that seems to indicate that there’s a bug in vba, and they recommend setting Application.Calculation = xlCalculationManual at the top of the worksheet and then Application.Calculation = xlCalculationAutomatic at the end. Frankly, I’m confused with the whole thing – it seems strange that I have code executing (the body within the range test writes a date to the end of my spreadsheet) when the condition isn’t met.
ah – the link is: http://www.excelforum.com/excel-programming/570066-problem-with-data-validation-dropdown-list-worksheet_change-event.html
Well…I’ve got it working – but I had to use the method above – once each time for each validation list. For whatever reason, I couldn’t group them into a combined non-contiguous range. For general changes (i.e., changes not requiring multiple clicks), I was able to use a grouped non-contiguous range. For multiple changes, I had to both include the Application.Calculation = xLCalculationManual and Automatic at the beginning and end of actions, and do individual range testing for each named range. Very odd. VBA has confounded me with its buggieness – particularly with built-in functions that simply don’t function properly (in a previous life I did C, Perl, GNU, SED/AWK, Unix Scripting, Fortran, Cobol, etc., programming – at least in those languages you knew why you had memory leaks and stack dumps). I’ve given up on the SpecialCells function entirely – it simply didn’t execute consistently enough – and I have to hand off this code to some non-programmers who aren’t going to tolerate occasional code malfunction. At any rate – I would not have known where to start without your code – so thank you very much for getting me off the ground floor.
Don, glad you got it working, and the SpecialCells limit seems to be fixed in Excel 2010.
Hi Debra
Your code for multiple selections from a drop down list into the same cell has been a life saver!!! I am having one small problem though…..
If I protect this spreadsheet so that others can’t edit formulas – you can no longer select multiple options in the samecell it overrides instead – any ideas on how to fix this?
Hi Debra,
I am sorry, but I am a newbie with this. Everything you offered above is clear but when I downloaded the sample data validation file into Excel 2010 and go to each tab to try the different options, the cell I select changes, i.e. 1.2.3… but it does not do what the code says, i.e. put in next cell, same cell with comma, …..
Is there something else I need to do to see each of the samples work?
thank you for your help.
Bill
BillJ, make sure that you enable macros when the file opens, and if you don’t see a security warning, you might need to change your security settings.
There are instructions here: http://blog.contextures.com/archives/2010/05/28/bypass-the-excel-macro-security-warning/