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.

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.

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

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.

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.

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.

_________________