Advanced Search

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.

Expand | Embed | Plain Text

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.