Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have created a Data Validation list, and I have added the VB code (below) to allow the user to select more than one of the choices in the list. The user can choose from the list or enter data (if none of the choices in the list fit). But, if the user chooses from the list and enters new data, the choices repeat.

For example, the list contains these choices: A, B, C, D, E. The user needs to choose A, B, and Z, so they choose A and B and enter Z but it then looks like this: A, B, Z, A, B

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 = 7 Or Target.Column = 14 Then
    If oldVal = "" Then
      'do nothing

    Else
      If newVal = "" Then
      'do nothing

    Else
      Target.Value = oldVal _
        & ", " & newVal
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

Help?

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.