Listbox Error " Could not Set the List property. Invalid Property Value."

Question

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

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.

Answer 1

Array method overcoming 10 columns limitation

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).

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.

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.

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.

Example code

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