Excel (VBA) drop down list multiple values in single cell


/ Published in: Visual Basic
Save to your folder(s)

Selecting different values from a data validation drop down list will populate the same cell, i.e. it will create a concatenating list separated by commas.
Amend the "If Target.Column" values to set the columns this applies to.


Copy this code and paste it in your HTML
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. ' Developed by Contextures Inc.
  3. ' www.contextures.com
  4. Dim rngDV As Range
  5. Dim oldVal As String
  6. Dim newVal As String
  7. If Target.Count > 1 Then GoTo exitHandler
  8.  
  9. On Error Resume Next
  10. Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
  11. On Error GoTo exitHandler
  12.  
  13. If rngDV Is Nothing Then GoTo exitHandler
  14.  
  15. If Intersect(Target, rngDV) Is Nothing Then
  16. 'do nothing
  17. Else
  18. Application.EnableEvents = False
  19. newVal = Target.Value
  20. Application.Undo
  21. oldVal = Target.Value
  22. Target.Value = newVal
  23. If Target.Column = 3 Then
  24. If oldVal = "" Then
  25. 'do nothing
  26. Else
  27. If newVal = "" Then
  28. 'do nothing
  29. Else
  30. Target.Value = oldVal _
  31. & ", " & newVal
  32. ' NOTE: you can use a line break,
  33. ' instead of a comma
  34. ' Target.Value = oldVal _
  35. ' & Chr(10) & newVal
  36. End If
  37. End If
  38. End If
  39. End If
  40.  
  41. exitHandler:
  42. Application.EnableEvents = True
  43. End Sub

URL: http://www.contextures.com/excel-data-validation-multiple.html

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.