Excel VBA如何实现不同区域下拉列表分别换行、逗号分隔显示

需求说明 #

• D3:D400、E3:E400区域的多选下拉菜单：选中的多个选项以换行符分隔，逐行展示在单元格内
• F3:F400区域的多选下拉菜单：选中的多个选项以逗号为分隔符，在同一行内拼接展示
• 原有代码对所有生效区域统一使用换行符拼接选项，无法满足F列的分隔要求，原有代码如下：

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

修改后代码 #

核心修改逻辑：新增分隔符变量，根据触发修改的单元格所属列匹配对应分隔符，其余原有逻辑（选项去重、事件开关、异常退出）全部保留，不需要调整其他数据验证配置。
注意：使用前请确认F列单元格已关闭「自动换行」属性，避免逗号分隔的内容被强制换行展示；D、E列单元格可开启「自动换行」保证多行内容正常显示。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Dim delimiter 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
  ElseIf Target.Value = "" Then
    GoTo Exitsub
  Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    ' 根据列号匹配对应分隔符
    If Target.Column = 6 Then ' F列对应列号为6，使用逗号+空格分隔
        delimiter = ", "
    Else ' D、E列使用换行符分隔
        delimiter = vbNewLine
    End If
    
    If Oldvalue = "" Then
        Target.Value = Newvalue
    Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & delimiter & Newvalue
        Else
            Target.Value = Oldvalue
        End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

使用说明 #

• 打开VBA编辑器的快捷键为Alt+F11，找到对应工作表的代码模块，替换原有Worksheet_Change事件代码即可生效
• D、E列单元格建议开启「设置单元格格式-对齐-自动换行」，保证多行选项完整展示
• F列如果需要逗号后不带空格，可将代码中delimiter = ", "修改为delimiter = ","

内容的提问来源于stack exchange，提问作者Megan