Excel VBA - 从数组填充多列用户表单列表框。当数组只有 1 项时没有数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39508799/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Populating multicolumn userform listbox from array. No data when array has only 1 item
提问by Nick
i have the following function that im using to populate a userform listbox with data from an array:
我有以下函数，我用它来用数组中的数据填充用户表单列表框：
Function PopulateListboxWithArray(lstbox As MSForms.ListBox, var As Variant)
With lstbox
If Not IsEmpty(var) Then
.Clear
.list = Application.Transpose(var)
.ListIndex = -1
End If
End With
End Function
My listbox contains two columns with the following properties:
我的列表框包含具有以下属性的两列：
PROBLEM
问题
The data in the array has an ID column and a lastname column. I dont want the user to see the ID column so ive set that column width to 0 in the form.
数组中的数据有一个 ID 列和一个姓氏列。我不希望用户看到 ID 列，因此我在表单中将该列的宽度设置为 0。
When i import data that has more than one row, the data shows up in the listbox as expected.
当我导入多于一行的数据时，数据按预期显示在列表框中。
However, when the array only contains one row of data, the listbox shows up blank !
但是，当数组只包含一行数据时，列表框显示为空白！
I have tried deleting the columnwidths in the image above and when i do so and reimport the one row of data, i get the ID and lastname stacked on top of one another. But ofcourse this is not the result i want.
我曾尝试删除上图中的列宽，当我这样做并重新导入一行数据时，我将 ID 和姓氏堆叠在一起。但这当然不是我想要的结果。
I have even tried replacing .list = Application.Transpose(var)with .list = varto no avail.
我甚至试图取代.list = Application.Transpose(var)与.list = var无济于事。
What am i doing wrong here, or is there a better way to populate a listbox?
我在这里做错了什么，还是有更好的方法来填充列表框？
cheers
干杯
采纳答案by Nick
I have found the answer in this post:Adding item in listbox with multiple columns
我在这篇文章中找到了答案：在具有多列的列表框中添加项目
I needed to use the .Listproperty My function now looks like this:
我需要使用.List属性我的函数现在看起来像这样：
Function PopulateListboxWithArray(lstbox As MSForms.ListBox, var As Variant)
With lstbox
If Not IsEmpty(var) Then
.Clear
If UBound(var, 2) > 0 Then
.list = Application.Transpose(var)
Else
.AddItem var(0, 0)
.list(.ListCount - 1, 1) = var(1, 0)
End If
End If
End With
End Function
回答by user3598756
editto add more "background"
编辑以添加更多“背景”
not so sure why you're using Application.Transpose()
不太确定你为什么使用 Application.Transpose()
With lstbox
If Not IsEmpty(var) Then
.Clear
If UBound(var, 1) = 1 Then
.AddItem
.List(0, 0) = var(1, 1)
.List(0, 1) = var(1, 2)
Else
' .List = Application.Transpose(var)
.List = var
End If
.ListIndex = -1
End If
End With
where I populated varin the following way:
我var用以下方式填充的地方：
Private Sub UserForm_Initialize()
Dim var As Variant
With Worksheets("LB") '<--| change "LB" to your actual sheet name
var = .Range("B1", .Cells(.rows.Count, "A").End(xlUp)).Value '<--| populate var with columns "A:B" cells values from row 1 down to column "A" last non empty row
End With
With Me.ListBox1
.ColumnCount = 2 '<--| set listbox columns count
.ColumnWidths = "0;144" '<--| set listbox columns width
End With
PopulateListboxWithArray Me.ListBox1, var
End Sub
