Excel VBA：列表框多选后文本框仅显示最后项的解决求助

原代码的核心问题在于：

• 内部嵌套的For i循环逻辑错误，会错误覆盖或拼接内容，且完全多余（列表框选项来自A列，可直接关联对应价格）
• 每次处理选中项时，没有对myVar和myprice进行累加拼接，而是直接赋值，导致最终只保留最后一个选中项

基础修复版代码 #

Private Sub lbxRooms_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim LastRow As Long
    Dim X As Integer
    Dim roomNum As String
    Dim myVar As String
    Dim myprice As String
    
    ' 初始化字符串
    myVar = ""
    myprice = ""
    
    ' 遍历列表框所有选项
    For X = 0 To Me.lbxRooms.ListCount - 1
        If Me.lbxRooms.Selected(X) Then
            roomNum = Me.lbxRooms.List(X, 0)
            ' 通过匹配房间号快速获取对应价格
            myprice = myprice & "," & Sheet3.Cells(WorksheetFunction.Match(roomNum, Sheet3.Range("A:A"), 0), "E").Value
            
            ' 拼接房间号，避免开头出现多余逗号
            If myVar = "" Then
                myVar = roomNum
            Else
                myVar = myVar & "," & roomNum
            End If
        End If
    Next X
    
    ' 移除开头多余的逗号（如果有选中项）
    If myVar <> "" Then myVar = Mid(myVar, 2)
    If myprice <> "" Then myprice = Mid(myprice, 2)
    
    ' 赋值到文本框
    Me.TextBox1 = myVar
    Me.TextBox2 = myprice
End Sub

高效优化版（推荐） #

如果在初始化列表框时同时加载A列和E列数据，可省去价格查找步骤，代码更高效：

1. 先添加用户窗体初始化代码 #

Private Sub UserForm_Initialize()
    Dim LastRow As Long
    LastRow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Row
    ' 设置列表框显示2列（房间号+价格）
    Me.lbxRooms.ColumnCount = 2
    ' 加载A列到E列数据，A列为第1列（索引0），E列为第5列（索引4）
    Me.lbxRooms.List = Sheet3.Range("A2:E" & LastRow).Value
    ' 隐藏价格列，只显示房间号
    Me.lbxRooms.ColumnWidths = "80;0"
End Sub

2. 简化多选事件代码 #

Private Sub lbxRooms_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim X As Integer
    Dim myVar As String
    Dim myprice As String
    
    myVar = ""
    myprice = ""
    
    For X = 0 To Me.lbxRooms.ListCount - 1
        If Me.lbxRooms.Selected(X) Then
            If myVar = "" Then
                myVar = Me.lbxRooms.List(X, 0)
                myprice = Me.lbxRooms.List(X, 4) ' 直接从列表框获取价格
            Else
                myVar = myVar & "," & Me.lbxRooms.List(X, 0)
                myprice = myprice & "," & Me.lbxRooms.List(X, 4)
            End If
        End If
    Next X
    
    Me.TextBox1 = myVar
    Me.TextBox2 = myprice
End Sub

关键修改说明 #

• 删除了多余的嵌套循环，改用匹配函数或列表框多列直接获取价格，避免逻辑混乱
• 对选中项进行累加拼接，确保所有选中的房间号和价格都被加入文本框
• 处理了字符串开头的多余逗号，保证输出格式整洁
• 优化版通过预加载数据彻底省去查找步骤，提升运行效率

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