如何修改VBA代码去除多选ComboBox选中项末尾的逗号？

给你三种简单的修改方案，任选一种都能解决问题：

方案1：用数组+Join函数（最推荐，代码简洁不易出错） #

先把选中的项存到数组里，再用Join函数直接生成逗号分隔的字符串，完全不用手动处理逗号：

Dim x As Long
Dim y As Long
Dim Homeroomselected As String
Dim selectedItems() As String
Dim itemCount As Long

For y = 2 To x
    itemCount = 0
    ' 重置数组
    ReDim selectedItems(0 To lstHomeroom.ListCount - 1)
    
    ' 收集选中项
    For Z = 0 To lstHomeroom.ListCount - 1
        If lstHomeroom.Selected(Z) = True Then
            selectedItems(itemCount) = lstHomeroom.List(Z)
            itemCount = itemCount + 1
        End If
    Next Z
    
    ' 调整数组长度并拼接
    If itemCount > 0 Then
        ReDim Preserve selectedItems(0 To itemCount - 1)
        Homeroomselected = Join(selectedItems, ",")
    Else
        Homeroomselected = ""
    End If
    
    Sheets("Input").Cells(y, 4).Value = Homeroomselected
Next y

方案2：判断后再添加逗号（避免开头或末尾多逗号） #

只有当已经有选中项时，才在新项前面加逗号，这样就不会在最后留逗号：

Dim x As Long
Dim y As Long
Dim Homeroomselected As String

For y = 2 To x
    Homeroomselected = "" ' 每次循环重置字符串
    'Update Homeroom
    For Z = 0 To lstHomeroom.ListCount - 1
        If lstHomeroom.Selected(Z) = True Then
            ' 如果已有内容，先加逗号再追加新项
            If Homeroomselected <> "" Then
                Homeroomselected = Homeroomselected & ","
            End If
            Homeroomselected = Homeroomselected & lstHomeroom.List(Z)
        End If
    Next Z
    Sheets("Input").Cells(y, 4).Value = Homeroomselected
Next y

方案3：拼接完成后去掉末尾逗号（简单直接） #

如果不想改循环逻辑，就在最后判断字符串长度，去掉最后一个逗号：

Dim x As Long
Dim y As Long
Dim Homeroomselected As String

For y = 2 To x
    Homeroomselected = "" ' 每次循环重置字符串
    'Update Homeroom
    For Z = 0 To lstHomeroom.ListCount - 1
        If lstHomeroom.Selected(Z) = True Then
            Homeroomselected = Homeroomselected & lstHomeroom.List(Z) & ","
        End If
    Next Z
    
    ' 去掉末尾的逗号
    If Len(Homeroomselected) > 0 Then
        Homeroomselected = Left(Homeroomselected, Len(Homeroomselected) - 1)
    End If
    
    Sheets("Input").Cells(y, 4).Value = Homeroomselected
Next y

注意：原代码里Homeroomselected没有在每次y循环时重置，可能导致多次循环后内容叠加，上面的三种方案都加上了重置操作，避免这个问题。

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