在Excel工作表上填充列表框的第2+列 [英] Populating 2nd+ column of listbox on Excel worksheet

问题描述

我在Excel 2007工作表上有一个ActiveX列表框.我想直接填充它，而不是通过将其RowSource属性指向某个范围来填充，因为没有范围具有期望的值.

I have an ActiveX listbox on an Excel 2007 worksheet. I want to populate it directly, not by pointing its RowSource property to a range, because there is no range that has the desired values.

列表框的ColumnCount设置为2. 我将ColumnWidths设置为"20; 20"，现在它返回: 20点； 20点

The listbox's ColumnCount is set to 2. I set ColumnWidths to "20;20", and now it returns: 20 pt;20 pt

据我了解，列表框中的两列应该可以写，对吧?

So as far as I understand, two columns in the listbox should be available for writing, right?

填充第一列没问题:

activesheet.lstApplyCurves.List = array("Select All","Deselect All","aaa","bbb","ccc")

(或)

activesheet.lstApplyCurves.additem
activesheet.lstApplyCurves.List(0,0) = "Col1, Row1"

但是如何填充第2列?我收到关于此的错误380(无法设置列表属性.无效的属性值."):

But how do I populate column 2? I get an error 380 ("Could not set the list property. Invalid property value.") on this:

activesheet.lstApplyCurves.List(0,1) = "Col2, Row1"

FWIW我也尝试过此方法，但会遇到相同的错误:

FWIW I've also tried this, but get the same error:

activesheet.lstApplyCurves.List(1,1) = "Col2, Row2"

那么...如何在第二列中设置值?

So...how do I set values in the 2nd column?

更新:

除了下面的答案，FWIW我还发现可以为List属性分配一个多维数组，这更快:

In addition to the answer below, FWIW I also found it's possible to assign a mulit-dimensional array to the List property, which is faster:

Dim ArrayToListbox() As Variant
ReDim ArrayToListbox(0 To 4, 0 To 2)
ArrayToListbox(0, 0) = "Select All"
ArrayToListbox(1, 0) = "Deselect All"
ArrayToListbox(2, 0) = "Row1-Col1"
ArrayToListbox(2, 1) = "Row1-Col2"
ArrayToListbox(2, 2) = "Row1-Col3"
ArrayToListbox(3, 0) = "Row2-Col1"
ArrayToListbox(3, 1) = "Row2-Col2"
ArrayToListbox(3, 2) = "Row2-Col3"
ArrayToListbox(4, 0) = "Row3-Col1"
ArrayToListbox(4, 1) = "Row3-Col2"
ArrayToListbox(4, 2) = "Row3-Col3" '"(" & Join(Array("a", "b", "c"), "|") & ")"
ActiveSheet.lstApplyCurves.Clear
ActiveSheet.lstApplyCurves.ColumnCount = 3
ActiveSheet.lstApplyCurves.List = ArrayToListbox

推荐答案

这对我有用.如果以下内容在您的系统上不起作用，请删除列表框，然后重新创建它，然后重试此代码.

This works for me. If the below doesn't work on your system then delete the listbox and re-create it and then try this code again.

Private Sub CommandButton1_Click()
    With ListBox1
        .Clear
        .ColumnCount = 2

        For i = 1 To 2
            .AddItem
            .List(i - 1, 0) = "Col1, Row" & i
            .List(i - 1, 1) = "Col2, Row" & i
        Next i

    End With
End Sub

这篇关于在Excel工作表上填充列表框的第2+列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！