Data Validation Multiple Selection Edit

If you select an item from a data validation drop down list, it’s entered in the cell, replacing any existing data in the cell. If you’d like to add more items to the cell, instead of replacing the current text, you can use a bit of programming, to allow multiple selections from a data validation list.

In my sample file that shows how to do this, I’ve added new code, to prevent a problem that occurs in one type of situation.

Drop Down List Multiple Entries http://blog.contextures.com/

Select Multiple Items From Drop Down List

In the screen shot below, you can see the “SameCell” worksheet, in my sample file. There is a data validation drop down list in the active cell.

From that list, “One” was selected, then “Two”, and now “Three” will be added. The code remembers the previous contents of the cell, and adds the new selection at the end.

multiselectenter01

Problems When Editing

The multiple selection technique works nicely, unless you try to manually edit the cell. After selecting a couple of items from the drop down list, I clicked in the cell, at the end of the existing text. Then I typed a comma, and “Three”.

Instead of just adding the new item, the code duplicated all the existing text, and then added then new item, so the result is “One, Two, One, Two, Three”.

multiselectenter02

Allow Manual Additions

If you want to manually add new items at the end of text in a cell, you can use the code from the “SameCellEnter” sheet in my sample file.

  • It gets the length of the old entry in the cell, and stores that number in a variable named “lOld”.
  • Then, it compares that number of character at the left of the new value to old value
  • If those strings are the same, it puts the new value into the cell
  • If those strings are NOT the same, it puts the old value, and a comma, and the new value into the cell

Here is the section of code that does the checking:

If newVal = "" Then
  'do nothing
Else
  lOld = Len(oldVal)
  If Left(newVal, lOld) = oldVal Then
    Target.Value = newVal
  Else
     Target.Value = oldVal _
          & ", " & newVal
  End If
End If

In this example,

  • oldVal = “One, Two
  • lOld=8
  • newVal = “One, Two, Three”

The first 8 characters at the left of newVal are equal to oldVal, so only the newVal text will be put into the cell after editing.

multiselectenter04

Allow Edits Anywhere in Text

The new code on the SameCellEnter sheet works well if you’re adding items at the end of the existing text in a cell.

If you want to edit the cell’s text, or put new entries between existing items, use the code from the “SameCellEdit” sheet in the sample file.

That sheet has an “Edit Entries” check box that you can click, to turn the macro code on or off. When the code is off, you can edit the cell manually. Then, remove the check mark from the “Edit Entries” check box, to turn the macro back on.

multiselectenter03

Get the Sample File

To get the new code, and other variations, go to the allow multiple selections from a data validation list page on my Contextures website. Download the free sample file there, and this example is on the Same Cell Enter sheet. To see the code, right-click the sheet tab, and click View Code.

NOTE: To make data entry even easier, you can buy a copy of my Data Validation Multi Select Premium kit. Use it to build lists that appear in a popup box. The lists can be set up for single selection or multiple selections, and your co-workers don’t need the kit in order to use the workbooks that you create.

_________________

Drop Down List Multiple Entries http://blog.contextures.com/

You may also like...

3 Responses

  1. Sigridur Juliusdottir says:

    Hi I tried to add the “Remove previously selected” code that you show in one of your videos, but the last part of one string is missing and because for some reasons the Allow multiple values code doesn’t work in my excel workbook with , separator only Chr(10) separator, I lack the skill and imagination to build the rest of the code… Here is what I’ve copied from you:

    Private Sub Worksheet_Change(ByVal Target As Range)
    ‘ Developed by Contextures Inc.
    http://www.contextures.com
    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 Target.Column = 11 Then
    If oldVal = “” Then
    ‘do nothing
    Else
    If newVal = “” Then
    ‘do nothing
    Else
    lUsed = InStr(1, oldVal, newVal)
    If lUsed > 0 Then
    If Right(oldVal, Len(newVal)) = newVal Then
    Target.Value = Left(oldVal, Len(oldVal) – Len(newVal) & Chr(10), “”)
    Else
    Target.Value = Replace(oldVal, newVal & Chr(10), “”)
    End If
    Else
    Target.Value = oldVal _
    & Chr(10) & newVal
    End If
    End If
    End If
    End If
    End If

    exitHandler:
    Application.EnableEvents = True
    End Sub

    Could you help me fix this?
    Kind regards,
    Sigridur J.

    • @Sigridur, change this code:
      Target.Value = Left(oldVal, Len(oldVal) – Len(newVal) & Chr(10), “”)
      to this:
      Target.Value = Left(oldVal, _
      Len(oldVal) – Len(newVal & Chr(10)))

      Also, you should add this at the top, with the other Dim statements:
      Dim lUsed As Long

  1. October 26, 2015

    […] Data Validation Multiple Selection Edit […]

Leave a Reply to Debra Dalgleish Cancel reply

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