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

____________________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

3 comments to Enter Multiple Items in an Excel Cell

  • Kap

    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.

  • Jitendraq

    Is this possible without VBA ?

  • Jackie

    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

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>