How do I make one dropdown list show as a new line and one as a separated comma list?-CodePudding

Working in excel, I'd like to make several dropdown menus (D3:D400 & E3:E400) a list with a new line each and one(F3:F400) that's text separated by a comma, all on one line. Current code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("D3:D400,E3:E400,F3:F400")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

CodePudding user response：

Use If Not Intersect(...) Is Nothing to check if the Target is in "D:E" or "F:F", and then create a variable Separator that is set to either vbNewLine or ", " depending on the result. Then later, join your strings with Separator like Target.Value = Oldvalue & Separator & Newvalue

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Not Intersect(Target, Range("D3:D400,E3:E400,F3:F400")) Is Nothing Then
        Dim Separator As String
        If Not Intersect(Target, Range("F3:F400")) Is Nothing Then
            Separator = ", "
        Else
            Separator = vbNewLine
        End If
        
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing _
        Or Target.Value = "" _
        Then
            GoTo Exitsub
        Else
            Application.EnableEvents = False
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
            If Oldvalue = "" Then
                Target.Value = Newvalue
            ElseIf InStr(1, Oldvalue, Newvalue) = 0 Then
                Target.Value = Oldvalue & Separator & Newvalue
            Else
                Target.Value = Oldvalue
            End If
        End If
    End If
Exitsub:
    Application.EnableEvents = True
End Sub