I am trying to build a drop-down box within Excel that allows for multiple selections. I was able to use some code provided by Contextures (http://www.contextures.com/excel-dat....html#Multiple) to do this. The one addition I would like to make is to also allow users to type in values in addition to being able to choose from the drop down list. However, whenever this code is used in conjunction with allowing users to type in their own responses, the code somehow causes the entry to duplicate the prior entry. For example, if the user selects "Apples" and "Oranges" from the drop down list, the cell will display, "Apples, Oranges". However, if the user now types in the response "Bananas", the cell will now display "Apples, Oranges, Apples, Oranges, Bananas". The way the code is set up, it is somehow copying the original value, and just inserting it before the new value. I'm not sure how to correct this so that the end product only reads "Apples, Bananas, Oranges".
Any suggestions? I've pasted the code I'm using below.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
'run code if only one cell was changed
If Target.Count > 1 Then GoTo exitHandler
Select Case Target.Column
Case 5 'this Case line works for column E only
On Error Resume Next
'check the cell for data validation
Set rngDV = Target.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
If newVal <> "" And newVal <> oldVal Then
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End Select
exitHandler:
Application.EnableEvents = True
End Sub
Bookmarks