如何在VBA用户窗体ListBox添加超10列？为何仅显示10列？

问题描述 #

我的UserForm包含ComboBox、TextBox和ListBox，已通过代码将ListBox的ColumnCount设为39，但实际仅显示前10列，相关VBA代码如下：

UserForm初始化代码 #

Private Sub UserForm_Initialize()
Me.BackColor = RGB(22, 54, 92)
Me.Label1.ForeColor = RGB(255, 255, 255)
Me.Label2.ForeColor = RGB(255, 255, 255)

Dim c As Integer
For c = 1 To 2
Me.ComboBox1.AddItem Sheet7.Cells(1, c).Value
Next

End Sub

ComboBox变更事件代码 #

Private Sub ComboBox1_Change()
    
    Dim c As Integer
    Dim column_headers
    column_headers = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM")
    
    For c = 1 To 39
    If Sheet7.Cells(1, c).Value = Me.ComboBox1.Value Then
    criterion = column_headers(c - 1)
    End If
    Next
    
    Me.ListBox1.Clear
    Me.TextBox1.Value = ""
    Me.TextBox1.SetFocus
    
End Sub

TextBox变更事件代码 #

Private Sub TextBox1_Change()
On Error Resume Next
If Me.TextBox1.Text = "" Then
Exit Sub
End If

Me.ListBox1.Clear
Dim r, last_row As Integer
last_row = Sheet7.Cells(Rows.Count, 2).End(xlUp).Row

With Me.ListBox1
    .ColumnCount = 39
End With

For r = 2 To last_row
    A = Len(Me.TextBox1.Text)
    If UCase(Left(Sheet7.Cells(r, criterion).Value, A)) = UCase(Me.TextBox1.Text) Then
    
    With Me.ListBox1
    .AddItem Sheet7.Cells(r, 1).Value
    .List(.ListCount - 1, 1) = Sheet7.Cells(r, 2).Value
    .List(.ListCount - 1, 2) = Sheet7.Cells(r, 3).Value
    .List(.ListCount - 1, 3) = Sheet7.Cells(r, 4).Value
    .List(.ListCount - 1, 4) = Sheet7.Cells(r, 5).Value
    .List(.ListCount - 1, 5) = Sheet7.Cells(r, 6).Value
    .List(.ListCount - 1, 6) = Sheet7.Cells(r, 7).Value
    .List(.ListCount - 1, 7) = Sheet7.Cells(r, 8).Value
    .List(.ListCount - 1, 8) = Sheet7.Cells(r, 9).Value
    .List(.ListCount - 1, 9) = Sheet7.Cells(r, 10).Value
    .List(.ListCount - 1, 10) = Sheet7.Cells(r, 11).Value
    .List(.ListCount - 1, 11) = Sheet7.Cells(r, 12).Value
    .List(.ListCount - 1, 12) = Sheet7.Cells(r, 13).Value
    .List(.ListCount - 1, 13) = Sheet7.Cells(r, 14).Value
    .List(.ListCount - 1, 14) = Sheet7.Cells(r, 15).Value
    .List(.ListCount - 1, 15) = Sheet7.Cells(r, 16).Value
    .List(.ListCount - 1, 16) = Sheet7.Cells(r, 17).Value
    .List(.ListCount - 1, 17) = Sheet7.Cells(r, 18).Value
    .List(.ListCount - 1, 18) = Sheet7.Cells(r, 19).Value
    .List(.ListCount - 1, 19) = Sheet7.Cells(r, 20).Value
    .List(.ListCount - 1, 20) = Sheet7.Cells(r, 21).Value
    .List(.ListCount - 1, 21) = Sheet7.Cells(r, 22).Value
    .List(.ListCount - 1, 22) = Sheet7.Cells(r, 23).Value
    .List(.ListCount - 1, 23) = Sheet7.Cells(r, 24).Value
    .List(.ListCount - 1, 24) = Sheet7.Cells(r, 25).Value
    .List(.ListCount - 1, 25) = Sheet7.Cells(r, 26).Value
    .List(.ListCount - 1, 26) = Sheet7.Cells(r, 27).Value
    .List(.ListCount - 1, 27) = Sheet7.Cells(r, 28).Value
    .List(.ListCount - 1, 28) = Sheet7.Cells(r, 29).Value
    .List(.ListCount - 1, 29) = Sheet7.Cells(r, 30).Value
    .List(.ListCount - 1, 30) = Sheet7.Cells(r, 31).Value
    .List(.ListCount - 1, 31) = Sheet7.Cells(r, 32).Value
    .List(.ListCount - 1, 32) = Sheet7.Cells(r, 33).Value
    .List(.ListCount - 1, 33) = Sheet7.Cells(r, 34).Value
    .List(.ListCount - 1, 34) = Sheet7.Cells(r, 35).Value
    .List(.ListCount - 1, 35) = Sheet7.Cells(r, 36).Value
    .List(.ListCount - 1, 36) = Sheet7.Cells(r, 37).Value
    .List(.ListCount - 1, 37) = Sheet7.Cells(r, 38).Value
    .List(.ListCount - 1, 38) = Sheet7.Cells(r, 39).Value
   
End If
Next r
End Sub

问题原因 #

这是因为Excel VBA ListBox的ColumnWidths属性默认仅给前10列分配了可见宽度（约15磅），第11列及以后的列宽度默认设为0，因此即便ColumnCount设为39，这些列也会被隐藏。另外，如果ListBox整体宽度不足，就算设置了列宽，超出部分也会被截断。

解决步骤 #

1. 设置ColumnWidths属性 #

可以在UserForm设计界面的属性窗口手动设置，也可以通过代码动态分配列宽。比如给39列每列分配50磅宽度：

' 在UserForm_Initialize或TextBox1_Change中添加
With Me.ListBox1
    .ColumnCount = 39
    ' 生成39个50的列宽值，用逗号分隔
    .ColumnWidths = String(38, "50,") & "50"
End With

如果需要不同列宽，直接修改数值即可，例如.ColumnWidths = "100,50,50,...,50"。

2. 开启水平滚动条 #

若ListBox宽度不足以显示所有列，开启水平滚动条可让用户拖动查看：

With Me.ListBox1
    .ScrollBars = fmScrollBarsHorizontal ' 也可设为fmScrollBarsBoth同时开启垂直和水平滚动条
End With

3. 优化ListBox数据添加代码（可选） #

当前手动写39行添加列的代码过于繁琐，可用循环简化：

With Me.ListBox1
    .AddItem Sheet7.Cells(r, 1).Value
    ' 循环添加第2到39列数据
    For col = 2 To 39
        .List(.ListCount - 1, col - 1) = Sheet7.Cells(r, col).Value
    Next col
End With

修改后的TextBox1_Change示例代码 #

Private Sub TextBox1_Change()
On Error Resume Next
If Me.TextBox1.Text = "" Then
Exit Sub
End If

Me.ListBox1.Clear
Dim r, last_row As Integer, col As Integer
last_row = Sheet7.Cells(Rows.Count, 2).End(xlUp).Row

With Me.ListBox1
    .ColumnCount = 39
    .ColumnWidths = String(38, "50,") & "50" ' 设置39列每列50磅宽度
    .ScrollBars = fmScrollBarsHorizontal ' 开启水平滚动条
End With

For r = 2 To last_row
    A = Len(Me.TextBox1.Text)
    If UCase(Left(Sheet7.Cells(r, criterion).Value, A)) = UCase(Me.TextBox1.Text) Then
    
    With Me.ListBox1
        .AddItem Sheet7.Cells(r, 1).Value
        ' 循环添加剩余列数据
        For col = 2 To 39
            .List(.ListCount - 1, col - 1) = Sheet7.Cells(r, col).Value
        Next col
    End With
   
End If
Next r
End Sub

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