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

本文介绍了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