Add a row with more than 10 columns to a ListBox?

[onmyway]

Hi guys,

i hope some one can help me. i am really stuck.

I have posted this elsewhere, but as I am struggling to get a working solution, I am also posting here in the hopes of a breakthrough. I will re-link etc. (http://www.excelforum.com/excel-prog...a-listbox.html)

Detail:
i have 12 controls/fields used for capturing data via a UserForm, updating a ListBox that is part of the same UserForm.
However, the problem lies in this: I can only capture 10 columns to the ListBox. I get an error every time the code loops from 10 to the 11th column.

Error: 380: Could not set List property, Invalid Property value.

My code is very simple:

Private Sub cmdAdd_Click()


        Dim arrCtrls As Variant
        Dim i As Long
        Dim LastRowIndex As Long
        Dim currentReference As RowReference
        
arrCtrls = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, TextBox22, TextBox23, TextBox24, TextBox25, TextBox26, TextBox27)


            With ListBox1
                .ColumnCount = UBound(arrCtrls) + 2
                .AddItem
                           
                For i = 0 To UBound(arrCtrls) - 1
                    .List(.ListCount - 1, i) = arrCtrls(i).Value
                Next i
    
End With
End Sub

I have attached a sample workbook as well.

Thank you in advance!

[SamT]

split the columns between two listboxes side by side and synchronize the lists.
OR
Use a Hidden sheet to put the array in, then link the range to one listbox


ColumnCount Property


Specifies the number of columns to display in a list box or combo box.
Syntax
object.ColumnCount [= Long]
The ColumnCount property syntax has these parts:

Part	Description
object	Required. A valid object.
Long	Optional. Specifies the number of columns to display.

Remarks
If you set the ColumnCount property for a list box to 3 on an employee form, one column can list last names, another can list first names, and the third can list employee ID numbers.
Setting ColumnCount to 0 displays zero columns, and setting it to -1 displays all the available columns. For an unbound data source, there is a 10-column limit (0 to 9).

unbound
Describes a control that is not related to a worksheet cell. In contrast, a bound control is a data source for a worksheet cell that provides access to display and edit the value of a control.

[JKwan]

I don't think it is possible. 10 is the limit for the list box control.

[snb]

It's perfectly possible if you use the correct method

Private Sub UserForm_Initialize()
    ListBox1.List = Range("A1:Z100").Value
    ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
End Sub

Private Sub UserForm_Initialize()
   redim sp(200,40)
    ListBox1.List =sp
    ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
End Sub

See also: http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

[JKwan]

Interesting, thanks for the tip snb

[onmyway]

It's perfectly possible if you use the correct method

Private Sub UserForm_Initialize()
    ListBox1.List = Range("A1:Z100").Value
    ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
End Sub

Private Sub UserForm_Initialize()
   redim sp(200,40)
    ListBox1.List =sp
    ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
End Sub

See also: http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

Hi snb,

Thank you for your response, as well as the link to the additional resources - very valuable indeed.
However, it does not seem to address my unique issue, of filling a ListBox with an unbound data source (as explained by SamT) of more than 10 columns. The limit indeed seems to be 10.

i am intrigued however, to find out how to "split the columns between two listboxes side by side and synchronize the lists." as suggested by SamT.

[onmyway]

split the columns between two listboxes side by side and synchronize the lists.

@SamT, Thank you for your informative response. May you please give me more information on this suggestion to make use of 2 ListBoxes and synchronize them?

Thank you!

[snb]

However, it does not seem to address my unique issue

You definitely are mistaken.
See the attachment.

You'd better have a look over here:

http://www.snb-vba.eu/VBA_Userform_in_database_en.html

[SamT]

I just spent two hours going over your very badly named code. It took me that long just to figure out why you need so many columns.

Private Sub CommandButton1_Click()
    With UserForm1
        .TextBox1.Text = ListBox1.List(ListBox1.ListIndex)
        .TextBox2.Text = ListBox1.List(ListBox1.ListIndex, 1)
        .TextBox3.Text = ListBox1.List(ListBox1.ListIndex, 2)
        .TextBox4.Text = ListBox1.List(ListBox1.ListIndex, 3)
        .TextBox5.Text = ListBox1.List(ListBox1.ListIndex, 4)
        .TextBox6.Text = ListBox1.List(ListBox1.ListIndex, 5)
    End With
End Sub

Since you are only using TestBox1" as a display for the User to select from:
In "ListBox 1" keep one column, separate the itmes (in one string) with " | " . Keep all the items in all the Rows in an array. In cbtUpDate_Click

Private Sub CommandButton1_Click()
Dim RowNum
ArrIndex = Me.tbxRecords.ListIndex - 1
    With Me
        .tbxRecordRing1.Text = arrRecords(arrindex, 0)
        .TtbxRecordHole1.Text =arrRecords(arrindex, 1)
        .tbxRecordDepth.Text =arrRecords(arrindex, 2)
        .tbxRecordChangeCode14.Text = arrRecords(arrindex, 3)
        .tbxRecordChangeCode2.Text = arrRecords(arrindex, 4)
        .tbxRecordDescription1.Text = arrRecords(arrindex, 5)
    End With
' The names are out of order, but that is the way to name controls!
'If you want really well structured code. Create an Enum in modGlobals for all twelve of the items.
End Sub

Enum ItemArrayIndexNumber
ndxRecordLocation1 = 0
ndxRecordRing1
ndxRecordHole1
ndxRecordDepth1
ndxProductionCodes1
ndxProductionDetail1
ndxRecordChangeCode1
ndxRecordChangeCode2
ndxRecordDescription1
ndxRecordChangeCode1RT
ndxRecordChangeCode2RT
ndxRecordChangeCode2RT
End Enum
' I know, the names are out of order

This changes the sample code above to read like

.tbxRecordRing1.Text = arrRecords(arrindex, .ndxRecordRing1)

Note how this makes your code "Self Correcting".

"Look, I'm working on Recording Ring one. Yep, the index is Recording Ring one. OK!"

My finale bits of programming practices advice. name a Module holding you Global Variables "modGlobals," and prefix Class Module names with "cls" or "obj."

Public GlobalData(6000) As objRowReference
  'Instantiated in UserForm1 Private Sub cmdAdd_Click()
  'Used in:
    'UserForm1 Private Sub btnSave_Click()
    'UserForm1 Private Sub btnNew_Click()
    'UserForm1 Private Sub ListBox3_DblClick

This is atrocious Naming system, most especially when you don't even have 27 textbox controls.

arrCtrls = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, _
            TextBox22, TextBox23, TextBox24, TextBox25, TextBox26, TextBox27)

[onmyway]

Hi snb,

Thank you for the response!

I don't know if I am misunderstanding (very possibly!), or whether I perhaps did not explain properly.

I am not trying to load data from a sheet into my ListBox. I know that way you can have many columns.

I am trying to update a ListBox via more than 10 controls/textboxes in a UserForm, in the same userForm (I.e., I make us of an unbound data source - my UserForm). It seems in this scenario, one can only have a maximum of 10 columns. That is a ListBox limitation.

Am I correct?

[SamT]

snb and I both suggested that you use a "helper" worksheet. put the array on the sheet, and use RowSource.

[onmyway]

Hi SamT,

Thank you for the response, and apologies for the confusion.

My actual userform has 151 controls - this was just a sample I created for this purpose of seeking help updating a ListBox with more than 10 controls as columns. My actual working project makes use of column names with corresponding field names, lots of notes etc. I definitely note your suggestions, and appreciate it.

Can you perhaps give me some input as to how I can "split the columns between two listboxes side by side and synchronize the lists."

[onmyway]

snb and I both suggested that you use a "helper" worksheet. put the array on the sheet, and use RowSource.

Noted, thanks guys!

[snb]

As I showed you before you can load an array of any size into a listbox/combobox using the method .List.
If you use .additem (that is not designed for populating a lsitbox/combobox) you will encounter limitations.

I strongly advise never to use .Rowsource or .listfillrange.
I never advise to use a 'helper' worksheet.
And 'split the columns between two listboxes side by side and synchronize the lists.' is solving inability to use VBA properly.

But I strongly advise to learn more from my website on arrays and populating listboxes/comboboxes.

[onmyway]

I will do, thank you!

[SamT]

how I can "split the columns between two listboxes side by side and synchronize the lists."

Which ever control has the focus at any time it must set all the applicable properties of the other to its own properties' values. You would obviously not have a vertical scroll on the LH box.

you use a "helper" worksheet.

The "Helper worksheet" is a standard of my Projects. I find one to be extremely useful.

I literally cannot imagine why you would need an actual multicolumn list. The purpose of the Listbox's many columns is to make it easy for the programmer to assign values to it. Set the RowSource Property with one range address and the code is complete. The purpose of the ListBox itself is merely to show the User some data to select from.

Is it possible that you are getting fixated on the programmers POV and need to take the User's POV for a minute?

My actual userform has 151 controls

I have found it useful to create my own Custom Collections and by using meaningful Control.Tags and looping thru UserForm.Controls assigning controls to certain custom ControlCollections. By mandating that all controls and variables have 3 letter prefixes and all Range.Names have a consistent length prefix. I can use the For Each method and with a little string manipulation make all assignments in a one line loop.
Example:

For each Ctrl in IOControls
   Range("pers" & Right(Ctrl.Name, Len(Ctrl.Name) -3) = Ctrl 'All "pers" ranges are on the Sheet Personnel. "cli" ranges on Sheet Clients
Next Ctrl
'arrays are faster, but I find this more understandable. A lot of my clients modify and maintain the code themselves.

Never forget that you are wearing many crucial hats, Programmer, Project Manager, Graphic Artist, User Representative, Code tester, Form tester, et al. Take the time to wear each hat at least once a day.

Here is a little sub (written for beginners) that might be of service to you.

Sub Make_SheetQualified_RangeNames()
'Creates Worksheet Specific Range Names ("Sheet1!MyNamedRange")
'Requires Column Headers Start in Column1, Row1
'Requires Header Lables have no spaces
'Uses: Allows the use of the Identical Range Names on different sheets.

Dim Cel As Range
Dim ColAddr As String
Dim ShtName As String

With ActiveSheet
  ShtName = .Name
  Set Cel = .Range("A1")
  ColAddr = ColumnLetters_SamT(Cel)

    Do While Cel.Value <> ""
      .Names.Add _
          Name:=ShtName & "!" & Cel.Value, _
              RefersTo:="=" & ShtName & "!" & _
              "$" & ColAddr & "$2" & _
              ":$" & ColAddr & "$" & CStr(.Rows.Count)
     Set Cel = Cel.Offset(0, 1)
    Loop
End With
End Sub

Function ColumnLetters_SamT(Cel As Range) As String
'Returns the letters part of a Column Address
'See http://www.vbaexpress.com/forum/showthread.php?52676-Add-a-row-with-more-than-10-columns-to-a-ListBox

Dim ColAdd As String

ColAdd = Columns(Cel.Column).Address
ColumnLetters_SamT = Right(ColAdd, Len(ColAdd) - InStrRev(ColAdd, "$"))
End Function

[SamT]

I would really listen to snb. I am to snb as Robin is to Superman

[snb]

In the attachment:

Enter the search string; when complete, click the 'search' button.
Listbox3 will be populated.


@SamT

Don't make me

[onmyway]

Hi Guys,

Once again, I really appreciate your input, guidance and mentoring. This is often rare in the world of today.

I believe a lot of my frustration is caused by my specific use for my ListBox. I am not using it to display data in a workbook sheet, but to capture data to it, and then save to the worksheet.

The reason for this is simple: at the time this seemed to be the only solution for capturing multiple rows of data for a single record. A good example would be an invoice: you have standard data like name and address etc., but you then need space to capture multiple invoice line items per invoice. And this is my use for the ListBox in this case. I write my "line items" to it, and then save the "single" invoice with multiple line items to my DB. i can also recall and edit it.

If I had used another application such as MS Access, one would have multiple DBs and link it with an ID field etc. Not so simple in excel. But, I am a student, and learning from guys like you.

In SamT's words (sort off): I am the Robin to the Robin to the Batman.

Lastly, Leith Ross has been extremely helpful in finding a solution to my problem, and I believe I am just about there. You can check it out at: http://www.excelforum.com/showthread...20#post4082520

Blessings!

[SamT]

If I had used another application such as MS Access, one would have multiple DBs and link it with an ID field etc. Not so simple in excel. But, I am a student, and learning from guys like you

I have got to change my signature. I use Excel as a simple database with many fields many times.