Field Type Information Error

[akrshaw]

I have a Column titled Scope that is fed from a named range on another sheet.

I also have code that allows you to select multiple items from the list and they populate the cells putting each choice on a seperate line in the cell.

When I select 2 or more from the list I get an error "The Value in this cell is invalid or missing" It shows the multiple items but I get the Exclaimation Point Box.

When I choose Display Information Type I get a box that says:

J10
Data Type: List
Restriction: Value must match one of the listed items.
Blanks will be ignored and are automatically valid.

This is not the first time I have used this, I have another Column setup the same way on another sheet with no issues?

Sample Data:

Pursuit Scope
Shrek Donkey
Prince Charming
Mickey Donald
Pluto
Goofy

Code from Contextures:

Option Explicit
' Developed by Contextures Inc.
'

http://www.contextures.com

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 Target.Column = 10 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& vbLf & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub