ListBox(用户窗体)VBA中的多列

问题描述

我在用户窗体的列表框中显示多个列时遇到问题.一切正常，直到我的列数最大为10.

I have a problem with displaying multiple columns in a ListBox in my UserForm.Everything is working until my numbers of column is max 10.

我的代码:

Private Sub FindButton_Click()
    ListBoxResult.Clear
    ListBoxResult.ColumnCount = 14
    Dim RowNum As Long
    RowNum = 1
    Do Until Sheets("db").Cells(RowNum, 1).Value = ""
        If InStr(1, Sheets("db").Cells(RowNum, 2).Value, FindDMC.Value, vbTextCompare) > 0 Then
            On Error GoTo next1
            ListBoxResult.AddItem Sheets("db").Cells(RowNum, 1).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 2) = Sheets("db").Cells(RowNum, 2).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 3) = Sheets("db").Cells(RowNum, 3).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 4) = Sheets("db").Cells(RowNum, 4).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 5) = Sheets("db").Cells(RowNum, 5).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 6) = Sheets("db").Cells(RowNum, 6).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 7) = Sheets("db").Cells(RowNum, 7).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 8) = Sheets("db").Cells(RowNum, 8).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 9) = Sheets("db").Cells(RowNum, 9).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 10) = Sheets("db").Cells(RowNum, 10).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 11) = Sheets("db").Cells(RowNum, 11).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 12) = Sheets("db").Cells(RowNum, 12).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 13) = Sheets("db").Cells(RowNum, 13).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 14) = Sheets("db").Cells(RowNum, 14).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 15) = Sheets("db").Cells(RowNum, 15).Value
        End If
next1:
        RowNum = RowNum + 1
    Loop
End Sub

ListBoxResult.ColumnCount 且属性为14，列宽也可以.运行我的代码后，失败代码为运行时错误'380':无法设置List属性.无效的属性值.起初，我以为ListBoxes可能会限制列，但我在Internet上发现有60列的ListBoxes.

ListBoxResult.ColumnCount and properties is 14, also Column widths is ok.After runing my code the failure code is Run-time error '380': Could not set the List property. Invalid property value. At first, I was thinking that maybe ListBoxes have limits for columns, but I found ListBoxes with 60 columns on the Internet.

我也在尝试这种方法，但仍然无法正常工作:

I am trying also this, but still doesn't work:

Private Sub Browser_RMA_Initialize()
 
ListBoxResult.RowSource = "db!a1:z1"
ListBoxResult.ColumnCount = 14
ListBoxResult.ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50;50;50;50;"
ListBoxResult.ColumnHeads = True
 
End Sub

可以请你支持我吗?

推荐答案

分配给.列属性可避免转置

Assigning to .Columnproperty avoids transposing

作为@ Dy.Lee的有效且已被接受的数组方法(请参阅我的评论)的最新内容，我演示了一种方法来避免重复重命名 [4] 和转置[5] :

As late addition to @Dy.Lee 's valid and already accepted array approach (see my comment), I demonstrate a way how to avoid both repeated redimming [4] and transposing [5]:

Option Explicit                                  ' declaration head of UserForm code module

Private Sub FindButton_Click()
    '[0] where to search
    Const SearchCol As Long = 2                  ' get search items from 2nd column
    '[1] define data set
    Dim data As Variant
    data = Tabelle1.Range("A1").CurrentRegion    ' << change to your project's sheet Code(Name)
    Dim ii As Long: ii = UBound(data, 1)         ' row count
    Dim jj As Long: jj = UBound(data, 2)         ' column count
    '[2] provide for sufficient result rows (array with converted row : columns order)
    Dim results() As Variant
    ReDim Preserve results(1 To jj, 1 To ii)    ' redim up to maximum row count ii
    '[3] assign filtered data
    Dim i As Long, j As Integer, n As Long
    For i = 1 To ii
        If InStr(1, data(i, SearchCol), FindDMC.Value, vbTextCompare) > 0 Then
    ''  If data(i, SearchCol) = FindDMC.Value Then      ' exact findings
            n = n + 1
            For j = 1 To jj
                results(j, n) = data(i, j)
            Next
        End If
    Next i
    '[4] fill listbox with results
    With ListBoxResult
        .Clear
        .ColumnCount = 14
        .ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50;50;50;50;"
        If n Then
            '[4] redimension only a 2nd time (& last time)
            ReDim Preserve results(1 To jj, 1 To n)
            '[5] assign results to listbox'es .Column property
            .Column = results       ' << .Column property avoids unnecessary transposing
        End If
    End With
End Sub

这篇关于ListBox(用户窗体)VBA中的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！