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 so much for this. It is just what I have looking for. Please keep up the great tutorials on vba. This is great. You are the best!!! Thanks so much again.
I’m sorry but I am not following what to do here to put the Samecell and SeparateColumn codes on the same worksheet.
And is it possible to make the Samecell code work with multiple columns within the same worksheet?
Ok looks good but I am more inclined to use SeparateColumn on two columns in the same worksheet. Now would I just have to modify this part of the code to read
If Target.Column = 10 or If Target.Column = 43 Then
column 43 is column AQ
How would I modify the the iCol code to continue to include the previous code for column 10 AND also to say start from column BC, and looks to the left of that column
Please help. I have been trying to modify the code based on information in this forum but to no avail.
First I would like to use the Separated Columns in two places on my worksheet column J (10) and column AU. I have tried to modify the code:
If Target.Column = 10 or If Target.Column = 43 Then
That part worked but I would like to modify the iCol code, so it starts from column S and BH , and looks to the left of those columns.
This works great but I just need a little help with the code. I have been trying to modify but not sure yet. Please help
@KJW, you can use Select Case, and two additional variables, to set the columns for the search to the left:
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim iCol As Integer Dim iStopA As Long Dim iStopB As Long iStopA = 19 iStopB = 60 If Target.Count > 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then ‘do nothing Else Application.EnableEvents = False If Target.Value = “” Then GoTo exitHandler If Target.Validation.Value = True Then Select Case Target.Column Case 10 iCol = Cells(Target.Row, iStopA).End(xlToLeft).Column + 1 Case 43 iCol = Cells(Target.Row, iStopB).End(xlToLeft).Column + 1 Case Else GoTo exitHandler End Select Cells(Target.Row, iCol).Value = Target.Value Else MsgBox “Invalid entry” Target.Activate GoTo exitHandler End If End If exitHandler: Application.EnableEvents = True End SubFirstly: thanks for an excellent code! Secondly: something’s terribly wrong out there… I use Excel 2010 and SameCell code that is working perfectly as long as I myself open the file (saved in .xls format). Why, oh why, it does not work for anyone else opening the same file? The list and the data validation works fine but no multiple choices possible. Not on Excel 2007 nor 2010. Could you help, please?