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.
_________________
Thom & Bernicerene:
You didn’t say which of the Tabs on Debra’s examples you were using so I assumed it was one that was unprotected from the start. I used Sheet 1, the Comma Separated Tab for this example. You can Protect your sheet (make sure your selection cells are unlocked), and still get the code to work by adding a With statement, like so:
With Sheet1
.Unprotect
ActiveSheet.Protect
End With
You are going to sandwich the code between the With Statement putting the first direction right after these two lines of code in Sheet 1, as follows:
Dim rngDV As Range
If Target.Count > 1 Then GoTo exitHandler
With Sheet1
.Unprotect
Next, you want to re-protect your sheet when the code is finished so end putting the End With code just above the End Sub, as follows:
ActiveSheet.Protect
End With
End Sub
Obviously, if you are working in another sheet, you’ll need to change the Sheet 1 to whatever sheet you’re working in. The end code remains the same.
Save, and try it. This worked for me in Excel 2003 & 2007.
Cheers,
LB in GA
Debra, Thank you for providing these solutions. I would like to use the LineBreak and LineBreakAddSort examples; however I would like to apply the codes to specific data valadation columns. Please suggest a solution.
Thank you again
Hi Debra , I have tried to delete error entries in the same cell dropdown list with comma separator but I get more entries from the dropdown list instead. Would you be able to advise me on this. Thank you very much
Lyle, the SameCellEdit sheet is the only one with code that uses the EditMode cell.
You could revise the code on the SameCell sheet, to also check the EditMode cell’s value.
This only works if you don;t have password protection on the sheet. If you do it asks for the password.
Angela, you can add a line of code at the top of the procedure, to unprotect the worksheet, e.g.:
ActiveSheet.Unprotect Password:=”abc”
Then, in the exitHandler section, reprotect the sheet:
ActiveSheet.Protect Password:=”abc”