Enter Multiple Items in an Excel Cell

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. Here's how to enter multiple items in an Excel cell, if it doesn't have a drop down list.

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

____________________

0 thoughts on “Enter Multiple Items in an Excel Cell”

  1. 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.

  2. 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

  3. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.