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.
_________________
Debra,
Thank you for posting this information. Unfortunately, I can’t get any of the code to work. When I download your example and try to use the multiple selection, it also doesn’t work. Is it possible this code does not work for certain versions or excel or with certain settings in place? I don’t even know where to begin to solve this.
Thank you,
Jenna
Hi all. I have solved my problem by formatting the date field as text and then using the MONTH and YEAR functions to extract month and year for filtering. Wierd but it works!
Thanks anyway for all your help.
Hi this is great, thanks
1) I have 2 questions. I am trying to alter the “SeparateColumns” code so that instead of the value going into the adjacent cell it goes into a column I first predetermine. For example I want the values from C3 to first go into D9 then go across as normal. D3 to start off in D10 etc.
2) Also can this code be modified so that the values are only placed in the cells if a macro runs. So I can make a button that the user presses and only then the values are placed in the cells? Thanks.
Tom.
Is there any way to add a line feed to the comma delimiter? I want to show the different values in the same cell, but in same cell column format. Simply put, I want an “Alt Shift” after each comma or better yet, replace the comma delimiter.
Debra,
Thank you for all the help in the tutorials and the above thread.
In the SeperateRows example, how can I keep the most recent input in the data valuation dropdown cell from being diplayed? (I want H1:H3 to show, but not G1)
Mark
@Phil Culver:
Go to the vba code and replace
this line: & “, ” & Target.Value with this line:
& vbLf & Target.Value
You may want to make sure that the cell you pick from is formatted so that the choice stays in the middle of the cell or at the top. If you don’t, it looks a little funny tracking at the bottom of the cell, because it too, get’s larger along with the data output cell.
LB in GA