Return to Snippet

Revision: 70866
at August 10, 2016 01:54 by cueballrawn

Initial Code
Private Sub Worksheet_Change(ByVal Target As Range)
' Developed by Contextures Inc.
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
  Application.EnableEvents = False
  newVal = Target.Value
  oldVal = Target.Value
  Target.Value = newVal
  If Target.Column = 3 Then
    If oldVal = "" Then
      'do nothing
      If newVal = "" Then
      'do nothing
      Target.Value = oldVal _
        & ", " & newVal
'      NOTE: you can use a line break,
'      instead of a comma
'      Target.Value = oldVal _
'        & Chr(10) & newVal
      End If
    End If
  End If
End If

  Application.EnableEvents = True
End Sub

Initial URL

Initial Description
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.

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

Initial Tags
list, excel

Initial Language
Visual Basic