Excel VBA ListBox仅显示1列，添加的8列无法显示问题求助

我有一个名为CustomerList的ListBox，会根据CustomerName组合框的值更新。我希望它能根据组合框文本搜索并显示客户信息，目前“Cust ID”能正常显示和填充，但其他列似乎消失或隐藏了。我尝试过设置列宽，但没有效果。不过当我从ListBox中选择条目时，对应的输入框能填充正确的客户信息。

对应的VBA代码如下：

Private Sub CustomerName_Change()
    Dim i As Long
    Dim sh As Worksheet
        Set sh = ThisWorkbook.Sheets("Customer Database")
    Dim Last_Row As Long
        Last_Row = Worksheets("Customer Database").Cells(Rows.Count, 1).End(xlUp).Row
    Me.CustomerName = Format(StrConv(Me.CustomerName.Text, vbLowerCase))
        With Me.CustomerList
            .Clear
            .AddItem "Cust. ID"
            .List(0, 1) = "Customer Name"
            .List(0, 2) = "Customer Business"
            .List(0, 3) = "Customer Address"
            .List(0, 4) = "Customer Phone"
            .List(0, 5) = "Customer Email"
            .List(0, 6) = "Machine Serial#"
            .List(0, 7) = "Machine Serial#"
            .List(0, 8) = "Machine Serial#"
        End With
    Me.CustomerList.Selected(0) = True
        For i = 2 To Last_Row
        For X = 1 To Len(sh.Cells(i, 1))
        a = Me.CustomerName.TextLength
            If LCase(Mid(sh.Cells(i, 2), X, a)) = Me.CustomerName And Me.CustomerName <> "" Then
        With Me.CustomerList
            .AddItem sh.Cells(i, 3)
            .List(CustomerList.ListCount - 1, 0) = sh.Cells(i, 1)
            .List(CustomerList.ListCount - 1, 1) = sh.Cells(i, 2)
            .List(CustomerList.ListCount - 1, 2) = sh.Cells(i, 3)
            .List(CustomerList.ListCount - 1, 3) = sh.Cells(i, 4)
            .List(CustomerList.ListCount - 1, 4) = sh.Cells(i, 5)
            .List(CustomerList.ListCount - 1, 5) = sh.Cells(i, 6)
            .List(CustomerList.ListCount - 1, 6) = sh.Cells(i, 7)
            .List(CustomerList.ListCount - 1, 8) = sh.Cells(i, 8)
        End With
    End If
    Next X
    Next i
End Sub

1. 强制设置ListBox列数与列宽 #

ListBox默认仅显示1列，必须先明确指定列数和列宽才能让多列内容可见。在初始化ListBox的代码块中添加以下两行：

With Me.CustomerList
    .Clear
    .ColumnCount = 9 ' 对应你需要显示的9列数据
    .ColumnWidths = "80,120,120,150,100,150,100,100,100" ' 按需求调整每列宽度，单位为磅
    ' 后续添加表头的代码...
End With

2. 修正条目添加逻辑 #

当前代码用.AddItem sh.Cells(i,3)会把第3列内容默认放到ListBox第0列，后续又给第0列赋值sh.Cells(i,1)，导致索引混乱且列内容覆盖。改为先添加空条目再逐一赋值：

With Me.CustomerList
    .AddItem "" ' 添加空行
    .List(.ListCount - 1, 0) = sh.Cells(i, 1)
    .List(.ListCount - 1, 1) = sh.Cells(i, 2)
    .List(.ListCount - 1, 2) = sh.Cells(i, 3)
    .List(.ListCount - 1, 3) = sh.Cells(i, 4)
    .List(.ListCount - 1, 4) = sh.Cells(i, 5)
    .List(.ListCount - 1, 5) = sh.Cells(i, 6)
    .List(.ListCount - 1, 6) = sh.Cells(i, 7)
    .List(.ListCount - 1, 7) = sh.Cells(i, 8)
    .List(.ListCount - 1, 8) = sh.Cells(i, 9) ' 补充原代码遗漏的第8列赋值
End With

3. 优化搜索逻辑 #

原代码的双层循环会导致重复添加匹配条目，改用InStr函数直接判断客户名是否包含搜索文本即可：

' 替换原内层循环与判断逻辑
If Me.CustomerName <> "" Then
    If InStr(LCase(sh.Cells(i, 2).Value), Me.CustomerName.Text) > 0 Then
        ' 此处添加ListBox条目代码
    End If
End If

完整修正后的代码 #

Private Sub CustomerName_Change()
    Dim i As Long
    Dim sh As Worksheet
    Dim Last_Row As Long
    Dim searchText As String
    
    Set sh = ThisWorkbook.Sheets("Customer Database")
    Last_Row = sh.Cells(Rows.Count, 1).End(xlUp).Row
    searchText = LCase(Me.CustomerName.Text)
    
    With Me.CustomerList
        .Clear
        .ColumnCount = 9
        .ColumnWidths = "80,120,120,150,100,150,100,100,100"
        ' 添加表头
        .AddItem ""
        .List(0, 0) = "Cust. ID"
        .List(0, 1) = "Customer Name"
        .List(0, 2) = "Customer Business"
        .List(0, 3) = "Customer Address"
        .List(0, 4) = "Customer Phone"
        .List(0, 5) = "Customer Email"
        .List(0, 6) = "Machine Serial#"
        .List(0, 7) = "Machine Serial#"
        .List(0, 8) = "Machine Serial#"
    End With
    
    Me.CustomerList.Selected(0) = True
    
    If searchText <> "" Then
        For i = 2 To Last_Row
            If InStr(LCase(sh.Cells(i, 2).Value), searchText) > 0 Then
                With Me.CustomerList
                    .AddItem ""
                    .List(.ListCount - 1, 0) = sh.Cells(i, 1)
                    .List(.ListCount - 1, 1) = sh.Cells(i, 2)
                    .List(.ListCount - 1, 2) = sh.Cells(i, 3)
                    .List(.ListCount - 1, 3) = sh.Cells(i, 4)
                    .List(.ListCount - 1, 4) = sh.Cells(i, 5)
                    .List(.ListCount - 1, 5) = sh.Cells(i, 6)
                    .List(.ListCount - 1, 6) = sh.Cells(i, 7)
                    .List(.ListCount - 1, 7) = sh.Cells(i, 8)
                    .List(.ListCount - 1, 8) = sh.Cells(i, 9)
                End With
            End If
        Next i
    End If
End Sub

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