Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
  Application.EnableEvents = False
  newVal = Target.Value
  oldVal = Target.Value
  Target.Value = newVal

  If Target.Column = 7 Or Target.Column = 14 Then
    If oldVal = "" Then
      'do nothing

      If newVal = "" Then
      'do nothing

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

  Application.EnableEvents = True
End Sub


share|improve this question

Your Answer


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

Browse other questions tagged or ask your own question.