Enter Multiple Items in an Excel Cell

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.

datavalmultiselect01

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.

datavalmultiselect02

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.

datavalmultiselect03

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

____________________