Maximum number of columns in listbox

[JenniferN]

Hi everyone,

I was wondering what the maximum number of columns is that one is allowed to use in a listbox. I was trying to put 16 columns in a listbox by using the property column count on the listbox. 7 of these 16 I wanted to have hidden by setting the columnwidth to 0. The reason for this is that I want to display all 16 values in another form after the selection in the listbox. Since I've managed to get 10 of the 16 values working the way I want and then I get "Error 380 - Not possible to set Property List. Invalid property value" (translation from my first language since I don't use Excel with English version) I'm trying to figure out is there a limitation or is there just something wrong with my code?

 

[DanteAmor]

Sorry to send it to pieces, haha, I had not read full.
With the following you adjust the width of the columns.

Private Sub UserForm_Initialize()
    Dim Sh As Worksheet, Rng As Range, j As Long, anc As String
    
    Set Sh = Worksheets("Systemtest")
    With Sh
        Set Rng = .Range("A[COLOR=#0000ff]2[/COLOR]:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
        ListBox1.ColumnCount = [COLOR=#0000ff]60[/COLOR]
[COLOR=#0000ff]        ListBox1.ColumnHeads = True[/COLOR]
        
[COLOR=#0000ff]        For j = 1 To ListBox1.ColumnCount[/COLOR]
[COLOR=#0000ff]            anc = anc & Int(.Cells(1, j).Width) + 3 & "; "[/COLOR]
[COLOR=#0000ff]        Next[/COLOR]
        
[COLOR=#0000ff]        ListBox1.ColumnWidths = anc[/COLOR]
        ListBox1.RowSource = .Name & "!" & Rng.Resize(, ListBox1.ColumnCount).Address
    End With
End Sub

 

[DanteAmor]

I was trying to put 16 columns in a listbox

With the AddItem method you can only add 10 columns.

I want to edit this old post, to show how to add more than 10 columns with AddItem.
Columns are added first with the List property and can now be added with .Additem.

Private Sub UserForm_Initialize()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim r As Integer
    Set Sh = Worksheets("Systemtest")
    With Sh
        Set Rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    'To add more than 10 columns.
      ReDim b(1 To 1, 1 To 16)  '
      With ListBox1
        .ColumnCount = 16       '
        .List = b               '
        .Clear                  '
      End With
    '
    r = 0
    For Each Cell In Rng
        With Cell
            ListBox1.AddItem .Value
            ListBox1.List(r, 1) = .Offset(0, 1).Value
            ListBox1.List(r, 2) = .Offset(0, 2).Value
            ListBox1.List(r, 3) = .Offset(0, 3).Value
            ListBox1.List(r, 4) = .Offset(0, 4).Value
            ListBox1.List(r, 5) = .Offset(0, 5).Value
            ListBox1.List(r, 6) = .Offset(0, 6).Value
            ListBox1.List(r, 7) = .Offset(0, 7).Value
            ListBox1.List(r, 8) = .Offset(0, 8).Value
            ListBox1.List(r, 9) = .Offset(0, 9).Value
            ListBox1.List(r, 10) = .Offset(0, 10).Value
            ListBox1.List(r, 11) = .Offset(0, 11).Value
            ListBox1.List(r, 12) = .Offset(0, 12).Value
            ListBox1.List(r, 13) = .Offset(0, 14).Value
            ListBox1.List(r, 14) = .Offset(0, 13).Value
            ListBox1.List(r, 15) = .Offset(0, 15).Value
          End With
        r = r + 1
    Next Cell
End Sub

 

[cutun2610]

Please help me show 18 column in my list box. I had tried many ways
----- CODE FOR SEARCH BUTTON
Private Sub btnSearch_Click()
On Error Resume Next
Me.ListBox1.Clear
Me.ListBox1.AddItem Sheet1.Cells(1, "A")
For B = 2 To 18
Me.ListBox1.List(ListBox1.ListCount - 1, B - 1) = Sheet1.Cells(1, B)
Next B
Me.ListBox1.Selected(0) = True
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 1 To 18
a = Len(Me.txtSearch.Text)
If Left(Sheet1.Cells(i, x).Value, a) = Me.txtSearch.Text And Me.txtSearch.Text <> "" Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
For c = 1 To 18
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1).Value
Next c
End If
Next x
Next i
End Sub

---- CODE FOR LISTBOX
Private Sub ListBox1_Click()
On Error Resume Next
Me.txt1.Text = ListBox1.List(ListBox1.ListIndex, 0)
Me.txt2.Text = ListBox1.List(ListBox1.ListIndex, 1)
Me.txt3.Text = ListBox1.List(ListBox1.ListIndex, 2)
Me.txt4.Text = ListBox1.List(ListBox1.ListIndex, 3)
Me.txt5.Text = ListBox1.List(ListBox1.ListIndex, 4)
Me.txt6.Text = ListBox1.List(ListBox1.ListIndex, 5)
Me.txt7.Text = ListBox1.List(ListBox1.ListIndex, 6)
Me.txt8.Text = ListBox1.List(ListBox1.ListIndex, 7)
Me.txt9.Text = ListBox1.List(ListBox1.ListIndex, 8)
Me.txt10.Text = ListBox1.List(ListBox1.ListIndex, 9)
Me.txt11.Text = ListBox1.List(ListBox1.ListIndex, 10)
Me.txt12.Text = ListBox1.List(ListBox1.ListIndex, 11)
Me.txt13.Text = ListBox1.List(ListBox1.ListIndex, 12)
Me.txt14.Text = ListBox1.List(ListBox1.ListIndex, 13)
Me.txt15.Text = ListBox1.List(ListBox1.ListIndex, 14)
Me.txt16.Text = ListBox1.List(ListBox1.ListIndex, 15)
Me.txt17.Text = ListBox1.List(ListBox1.ListIndex, 16)
Me.txt18.Text = ListBox1.List(ListBox1.ListIndex, 17)

End Sub

 

[DanteAmor]

Hi and welcome to MrExcel.

I don't understand the logic of the search, you check the search text in all the columns of a row.

For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 1 To 18

If you find it, you add the whole row to the listbox.
But you keep looking for the text in the whole row, so if the text exists in 3 columns, you would be adding the same row 3 times.

Explain with an example what you need. Use the XL2BB tool minisheet to paste an example here.

 

[cutun2610]

Hi and welcome to MrExcel.

I don't understand the logic of the search, you check the search text in all the columns of a row.



If you find it, you add the whole row to the listbox.
But you keep looking for the text in the whole row, so if the text exists in 3 columns, you would be adding the same row 3 times.

Explain with an example what you need. Use the XL2BB tool minisheet to paste an example here.

Tks Bro ?? I will try this ??

 

[raz355]

I want to edit this old post, to show how to add more than 10 columns with AddItem.
Columns are added first with the List property and can now be added with .Additem.

Private Sub UserForm_Initialize()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim r As Integer
    Set Sh = Worksheets("Systemtest")
    With Sh
        Set Rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    'To add more than 10 columns.
      ReDim b(1 To 1, 1 To 16)  '
      With ListBox1
        .ColumnCount = 16       '
        .List = b               '
        .Clear                  '
      End With
    '
    r = 0
    For Each Cell In Rng
        With Cell
            ListBox1.AddItem .Value
            ListBox1.List(r, 1) = .Offset(0, 1).Value
            ListBox1.List(r, 2) = .Offset(0, 2).Value
            ListBox1.List(r, 3) = .Offset(0, 3).Value
            ListBox1.List(r, 4) = .Offset(0, 4).Value
            ListBox1.List(r, 5) = .Offset(0, 5).Value
            ListBox1.List(r, 6) = .Offset(0, 6).Value
            ListBox1.List(r, 7) = .Offset(0, 7).Value
            ListBox1.List(r, 8) = .Offset(0, 8).Value
            ListBox1.List(r, 9) = .Offset(0, 9).Value
            ListBox1.List(r, 10) = .Offset(0, 10).Value
            ListBox1.List(r, 11) = .Offset(0, 11).Value
            ListBox1.List(r, 12) = .Offset(0, 12).Value
            ListBox1.List(r, 13) = .Offset(0, 14).Value
            ListBox1.List(r, 14) = .Offset(0, 13).Value
            ListBox1.List(r, 15) = .Offset(0, 15).Value
          End With
        r = r + 1
    Next Cell
End Sub

Can you show to add textbox in there? Im still new around vba.
I have 3 textbox to search for different column, textbox1 for column C, textbox2 for column E and textbox3 for column K. As you can i see i have 11 column in total. Any help would be appreciated.

 

[Fluff]

@raz355 as your question is totally different to the original question, you need to start a new thread. Thanks

 

[raz355]

@Fluff got it