列表框错误“无法设置列表属性.无效的属性值." [英] Listbox Error " Could not Set the List property. Invalid Property Value."

问题描述

我有一个带有列表框，文本框和组合框以及保存按钮的用户窗体.下面是我的保存按钮代码.

I have userform with a Listbox, textbox and comboboxes and a Save button. Below is my save button code.

Private Sub cmdsave_Click()
Dim x As Integer
        x = Me.ListBox1.ListCount

If Me.cmbtrans.Value = "Debit" Then
    With Me.ListBox1
        .Enabled = True
        .ColumnCount = 13
        .ColumnWidths = "49.95 pt;10 pt;114.95 pt;10 pt;114.95 pt;10 pt;114.95 pt;10 pt;75 pt;10 pt;49.95 pt;10 pt;49.95 pt"
            .AddItem
            .List(x, 0) = Me.txtdate
            .List(x, 1) = "|"
            .List(x, 2) = Me.txtgrouphead
            .List(x, 3) = "|"
            .List(x, 4) = Me.txtcontrolhead
            .List(x, 5) = "|"
            .List(x, 6) = Me.cmbaccounthead
            .List(x, 7) = "|"
            .List(x, 8) = Me.cmbtrans
            .List(x, 9) = "|"
            .List(x, 10) = Me.txtamount
      End With

End If

End Sub

它在添加项目级别.List(x, 10) = Me.txtamount处显示错误. 但是，它运行得很顺利，直到添加项目级别.List(x, 9) = "|".

It shows the error at the Add item Level .List(x, 10) = Me.txtamount. however, it runs smoothly till add item level .List(x, 9) = "|".

我不明白为什么它在最后一行给出了错误.

I am unable to understand why it is giving error at last line.

推荐答案

克服10列限制的数组方法

.AddItem方法的局限性和默认设置仅为 10列，只能在列表框(或组合框)中创建； List索引从零开始，您最多只能添加.List(x, 9).

The .AddItem method has a limitation and default setting of only 10 columns that can be created in a Listbox (or Combobox); List indices are zero-based, you can only add up to .List(x, 9).

如果要克服此内置限制，则必须使用 Array方法，允许在一个语句中将整个数组分配给.List属性.

If you want to overcome this built in restriction you have to used the Array method allowing to assign a whole array to the .List property in one statement.

在每次单击事件时增加列表框行元素以添加新的控件值时，必须将整个数据集重新分配一个新的行元素.

As you are increasing the listbox row elements with each event click in order to add new control values, you have to redimension the whole data set by one new row element.

但是，ReDim Preserve语句只能在其最后一个维中执行.因此，您将必须使用2维数组，其中行"索引位于不变的列"索引之后，以提供尺寸正确的数组.

A ReDim Preserve statement, however, can only be executed in its last dimension. So you'll have to use a 2-dim array where the 'row' index follows after the invariable 'column' index to provide a correctly dimensioned array.

技巧:您可以使用.Column属性代替该数组，而不是重新放置此数组并将其分配回列表框.List属性，该属性已经接受了反向(=转置)维订单.

Trick: Instead of re-transposing this array and assigning it back to the listboxes .List property, you can use the .Column property instead which already accepts the inverted (=transposed) dimension order.

示例代码

添加了一个控件名称字符串以进一步方便，因为它允许通过Controls集合在循环中获取使用的控件值，该集合允许按名称引用它们.

Added a control names string for further convenience, as it allows to get the used control values in a loop via the Controls collection which allows to reference them by name.

Private Sub cmdSave_Click()
' Define a control names string to be used to append to Listbox1
  Const USEDCONTROLS$ = "txtdate,txtgrouphead,txtcontrolhead,cmbaccounthead,cmbtrans,txtamount"
  Dim x&, i&, v, myCtrls
  myCtrls = Split(USEDCONTROLS, ",")        ' create flat array myCtrls out of control names

  x = Me.ListBox1.ListCount                 ' get current list count

If Me.cmbtrans.value = "Debit" Then
  With Me.ListBox1
    .Enabled = True
    .ColumnWidths = "49.95 pt;10 pt;114.95 pt;10 pt;114.95 pt;10 pt;114.95 pt;10 pt;75 pt;10 pt;49.95 pt;10 pt;49.95 pt"
    .ColumnCount = 13

    If .ListCount > 0 Then
       ' [1] write existing listbox elements to array(column index, row index)
         v = .Column
       ' [2] increment to new row index x in 2nd (sic!) dimension
       '     as a ReDim Preserve can only change the last array dimension.
       '     (with the .Column property you are using the
       '     transposed counterpart of the .List property)
         ReDim Preserve v(UBound(v), x)
    Else
       ' [1-2] redimension array v the first time (no existing listbox values so long)
         ReDim v(.ColumnCount - 1, 0)           ' 13 counted columns equal a zerobased index of 12
    End If

    ' [3a] assign current values to array
      For i = 0 To UBound(myCtrls)
          v(i * 2, x) = Me.Controls(myCtrls(i))
      Next i
    ' [3b] write separators after each data input
      For i = 1 To UBound(v) - 1 Step 2: v(i, x) = "|": Next i

    ' [4] reassign array to listbox property .Column (~ transposed .List property)
    .Column = v
  End With

End If

End Sub

这篇关于列表框错误“无法设置列表属性.无效的属性值."的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！