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.
_________________
Hi Debra
Thank you so very much for this code. It makes laytechs like me look good! I found the tutorial which worked then realised I needed to apply it to different columns in a worksheet, so found these comments. Well done. Is it possible to apply different code for other columns in the same worksheet, eg SameCell and SeparateColumns?
Thank you so much for posting the instructions. Plus, having the code viewable. And the video (I’m a visual learner.) I work at a place where we cannot download anything from the ‘net. I typed the code in exactly as shown; changed the column number. Did the Data Validation. It worked perfectly. They think I am awesome. LOL! Thank you, thank you.
Hi Debra, your website is great. I’ve copied your “SameCellAddRemove” code. I’ve read above on how to limit or expand the code from one column to all columns. How do i go about limit to three specific columns, for example, columns 7,14 & 21? For example: If Target.Column = 3 Then
How do you change it to include 7,14 & 21?
@Michael, instead of If…End If you can use Select Case…End Select
Select Case Target.Column Case 7, 14, 21 If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If Case Else 'do nothing End SelectThanks Debra, apologies, I’m still having an issue. This is the code for “SameCellAddRemove” Where do I insert your solution above? Sorry!
Option Explicit ' Developed by Contextures Inc. ' www.contextures.com Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String Dim lUsed As Long 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 newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 3 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else lUsed = InStr(1, oldVal, newVal) If lUsed > 0 Then If Right(oldVal, Len(newVal)) = newVal Then Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2) Else Target.Value = Replace(oldVal, newVal & ", ", "") End If Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If End If exitHandler: Application.EnableEvents = True End Sub@Michael, here is the last section of the code with Select Case, instead of If
Target.Value = newVal Select Case Target.Column Case 7, 14, 21 If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else lUsed = InStr(1, oldVal, newVal) If lUsed > 0 Then If Right(oldVal, Len(newVal)) = newVal Then Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2) Else Target.Value = Replace(oldVal, newVal & ", ", "") End If Else Target.Value = oldVal _ & ", " & newVal End If End If End If Case Else 'do nothing End Select End If exitHandler: Application.EnableEvents = True End SubIt works! Thank you!! You are amazing. Have a good day
Hi, I have used the same code to select more than one items in same cell but it not working. I am using Excel 2007 even though I enabled the macros to run but still it not working.
@Raheel, are you having the problem with my sample file, or did you copy the code to one of your own workbooks, and it’s not working there?