Today we’ll see an interesting tweak to an old technique. One of the articles on my website shows how you can select multiple items from a cell’s drop down list. Instead of overwriting the cell’s value, new selections are added to the end of the cell’s contents. Here’s how to enter multiple items in an Excel cell, if it doesn’t have a drop down list.
Code Checks for Data Validation
Drop down lists are created with data validation, so the technique uses code to check the active cell, to make sure that it has data validation applied.
If there isn’t any data validation on the worksheet, or if the cell doesn’t have data validation, the multiple selection code doesn’t run.
Multiple Selections Without Data Validation
Last week, I received an email from Brian Walker, who had created a variation on the code. He wanted to enter multiple values in specific cells, where no data validation was applied.
So, instead of checking for data validation, he named the cells, then checked the active cell for that name. In the screen shot below, the blue cells are named – MVCell1 and MVCell2.
Multiple Selection Code
Here is the sample code that adds the latest entry to the existing contents. Brian uses a line break as a separator – Chr(10). You could change that to a comma and space – “, “ – if you prefer.
To use the code, copy it, and paste it onto the worksheet module where you want to use it. Remember to name the cells, and change the code to use your names.
Private Sub Worksheet_Change(ByVal Target As Range) Dim oldVal As String Dim newVal As String On Error Resume Next If Target.Count > 1 Then GoTo exitHandler Select Case Target.Name.Name Case "MVCell1", "MVCell2" Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If oldVal = "" Or newVal = "" Then 'do nothing Else Target.Value = oldVal & Chr(10) & newVal End If End Select exitHandler: Application.EnableEvents = True End Sub
____________________
May I please have step-by-step instructions to create a drop-down with two columns of data using Excel 2010:
Col-1 Col-2
1 $10.00
2 $12.00
3 $8.00
4 $9.00
I would like Col-1 to be linked to a cell within the sheet. Many thanks.
Is this possible without VBA ?
I am using the code below, but I cannot figure out how to remove a selection from the list – example, I select 3 values from the list, but then I want to go back and remove 1 value, how can I do this? It seems like something is overwriting this ability within the code, but I don’t know how to fix it.
Thanks,
Jackie
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
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 oldVal = ” ” Then
Else
If newVal = ” ” Then
Else
Target.Value = oldVal & “, ” & newVal
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Thanks for this article! I did further amend the Macro to make it work for my purpose
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rangeDV As Range
Dim oldValue As String
Dim newValue As String
If Target.Count > 1 Then GoTo finally
On Error Resume Next
Set rangeDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo finally
If rangeDV Is Nothing Then GoTo finally
If Not Intersect(Target, rangeDV) Is Nothing Then
Application.EnableEvents = False
newValue = Target.Value
Application.Undo
oldValue = Target.Value
Target.Value = newValue
If oldValue = “” Then
Target.Value = newValue
Else
If newValue = “” Then
Target.Value = “”
Else
Target.Value = oldValue & “, ” & newValue
End If
End If
End If
finally:
Application.EnableEvents = True
End Sub