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.
_________________
Thanks Debra
The works great something exactly what I was looking for. but as I had to change this part of the code: “If Target.Column = 10 Then” (Note I am using this code only for columns 10 (J) – columns 17 (Q)
but the problem is if column 10-17 (J-Q) are blank and column 18 (R) has data in it, when I choose the drop down to fill my columns (J-R) any other column that has data after column R gets filled with the data that is supposed to fill columns J-Q.
For example column J-R are empty and column S has data then the drop down of column J will fill column T and so on, with the data that is supposed to go in column J-Q.
How can the code be modified to specify that this is only to be used on Target column 10-17.
But I love it. It works great. Wish we could modify it.
I’m sorry but I am using the
Select Multiple Items From the Drop Down List to Fill Across a Row
with its associated code
Is it possible to use two set of codes in the same sheet because i want to use the samecell and the separate column in my workbook on the same sheet.
Can more than one of the be used on the same workbook and on the same sheet? How do I put the code on the same sheet?
@KJW, you can change the iCol code, so it starts from column S, and looks to the left of that column:
iCol = Cells(Target.Row, “S”).End(xlToLeft).Column + 1
@KJW, you can combine the two options within the same Worksheet_Change code. Use Select Case, as in the Separate Rows sheet code.
For example:
Select Case Target.Column
Case 8
‘code for same cell
Case 10
‘code for separate columns
End Select