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