列表框错误无法设置列表属性.无效的属性值." [英] 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-dim 数组，其中行"索引位于不变的列"索引之后，以提供正确尺寸的数组.

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屋！