Maximum number of columns in listbox

J

JenniferN

New Member
Joined
Dec 19, 2007
Messages
44
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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
There's probably something wrong with your code. I just filled a 26 column list box.
What code are you using to fill the listbox?
 
Upvote 0
Thanks for the fast reply. This is the code I'm using to fill my listbox and thereafter to show userform1 with the values
Code: 
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
    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

Private Sub OKButton2_Click()
    If ListBox1.ListIndex <> -1 Then
       'UserForm1.Label.Value = ListBox1.Column(0) "Skapa en etikett el. likn. som visar ID"
       UserForm1.DateBox.Value = ListBox1.Column(1)
       UserForm1.StatusBox.Value = ListBox1.Column(2)
       UserForm1.ClosingBox.Value = ListBox1.Column(3)
       UserForm1.HeadingBox.Value = ListBox1.Column(4)
       UserForm1.SummaryBox.Value = ListBox1.Column(5)
       UserForm1.CommentsBox.Value = ListBox1.Column(6)
       UserForm1.TestspecBox.Value = ListBox1.Column(7)
       UserForm1.SeverityBox.Value = ListBox1.Column(8)
       UserForm1.EnvBox.Value = ListBox1.Column(9)
       'UserForm1.VersionBox.Value = ListBox1.Column(10)
       'UserForm1.SubsysBox.Value = ListBox1.Column(11)
       'UserForm1.FormBox.Value = ListBox1.Column(12)
       'UserForm1.TesterBox.Value = ListBox1.Column(13)
       'UserForm1.ResponsibleBox.Value = ListBox1.Column(14)
       'UserForm1.FixedVerBox.Value = ListBox1.Column(15)    
    End If
    UserForm1.Show
End Sub

Private Sub CancelButton2_Click()
Unload UserForm2
End Sub
When I change the listbox' column 10 from comment to code that's when I get an error message, but if I just leave the code as it's shown above columns 0-9 are displayed without any error message.
 
Upvote 0
This worked for me. If you are using a Windows version, I'm not sure. The .ColumnCount wasn't specified in the other code.
Code: 
Private Sub UserForm_Initialize()
    Dim Sh As Worksheet
    Set Sh = Worksheets("Systemtest")
    
    With Sh
        Set Rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    
    Me.ListBox1.ColumnCount = 18
    
    Dim myArray As Variant
    myArray = Rng.Resize(, Me.ListBox1.ColumnCount).Value
    
    Me.ListBox1.List = myArray
End Sub
 
Upvote 0
I just set the columncount in the property for the listbox and not in the code. Maybe that was incorrect. However, thank you so much. You've made my day.:beerchug:
 
Upvote 0
mikerickson said:
This worked for me. If you are using a Windows version, I'm not sure. The .ColumnCount wasn't specified in the other code.
Code: 
Private Sub UserForm_Initialize()
    Dim Sh As Worksheet
    Set Sh = Worksheets("Systemtest")
    
    With Sh
        Set Rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    
    Me.ListBox1.ColumnCount = 18
    
    Dim myArray As Variant
    myArray = Rng.Resize(, Me.ListBox1.ColumnCount).Value
    
    Me.ListBox1.List = myArray
End Sub
Click to expand...

hi mikerickson

how code for columnheads listbox and columnwidth automatic fit?

thanks
 
Upvote 0
JenniferN said:
I just set the columncount in the property for the listbox and not in the code. Maybe that was incorrect. However, thank you so much. You've made my day.:beerchug:
Click to expand...
I believe the maximum columncount you can configure is actually only 10ish.

The reason Mickerson's code works is because he populated the list box using an Array. This allows you to exceed the maximum column count.
 
Last edited:
Upvote 0
Hi steve_,

i have 60 columncount in my listbox. After i use mickerson's code all column exceed well.
But header didn't show out and how i entirecolumn with autofit

thanks,
​
 
Upvote 0
JenniferN said:
Thanks for the fast reply. This is the code I'm using to fill my listbox and thereafter to show userform1 with the values
Code: 
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
    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

Private Sub OKButton2_Click()
    If ListBox1.ListIndex <> -1 Then
       'UserForm1.Label.Value = ListBox1.Column(0) "Skapa en etikett el. likn. som visar ID"
       UserForm1.DateBox.Value = ListBox1.Column(1)
       UserForm1.StatusBox.Value = ListBox1.Column(2)
       UserForm1.ClosingBox.Value = ListBox1.Column(3)
       UserForm1.HeadingBox.Value = ListBox1.Column(4)
       UserForm1.SummaryBox.Value = ListBox1.Column(5)
       UserForm1.CommentsBox.Value = ListBox1.Column(6)
       UserForm1.TestspecBox.Value = ListBox1.Column(7)
       UserForm1.SeverityBox.Value = ListBox1.Column(8)
       UserForm1.EnvBox.Value = ListBox1.Column(9)
       'UserForm1.VersionBox.Value = ListBox1.Column(10)
       'UserForm1.SubsysBox.Value = ListBox1.Column(11)
       'UserForm1.FormBox.Value = ListBox1.Column(12)
       'UserForm1.TesterBox.Value = ListBox1.Column(13)
       'UserForm1.ResponsibleBox.Value = ListBox1.Column(14)
       'UserForm1.FixedVerBox.Value = ListBox1.Column(15)    
    End If
    UserForm1.Show
End Sub

Private Sub CancelButton2_Click()
Unload UserForm2
End Sub
When I change the listbox' column 10 from comment to code that's when I get an error message, but if I just leave the code as it's shown above columns 0-9 are displayed without any error message.
Click to expand...


With the AddItem method you can only add 10 columns.
If you want more than 10 columns then you can use the List or RowSource property
 
Upvote 0
phunguyen said:
Hi steve_,

i have 60 columncount in my listbox. After i use mickerson's code all column exceed well.
But header didn't show out and how i entirecolumn with autofit

thanks,
Click to expand...

To put the heading, assuming it are in row 1
Code: 
Private Sub UserForm_Initialize()
    Dim Sh As Worksheet, Rng As Range
    Set Sh = Worksheets("Systemtest")
    With Sh
        Set Rng = .Range("A[B][COLOR=#0000ff]2[/COLOR][/B]:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
        ListBox1.ColumnCount = [COLOR=#0000ff]60[/COLOR]
        ListBox1.ColumnHeads = True
        ListBox1.RowSource = .Name & "!" & Rng.Resize(, ListBox1.ColumnCount).Address
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Error setting List Property of Userform Listbox With VBA
Replies
4
Views
58
Ark68
A
I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
354
Fuji
F
A
  • Question Question
Listbox scroll position - refreshing
Replies
7
Views
132
AlexanderBB
A
J
  • Question Question
Filter ListBox on a UserForm using a ComboBox
Replies
1
Views
388
DanteAmor
DanteAmor
P
  • Solved
run time error 380 rowsource property invalid property value
2 3
Replies
24
Views
856
Patriot2879
P

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top