More than 10 columns VBA

[TimdeBlaeij]

Hi,

I am working on a program which is supposed to put columns S to AO in a listbox. I get a error message when I try to add more than 10 colomns. Does anybody know how i can fix this?
Also my scroller for my listbox is way bigger than needed. it goes far beyond my last filled in row.
Also i would like my top row to be visivle at all times when scrolling trough the listbox since it defines the rows. Anybody got something for these things too?


~Couple of things that you might want to know~
-All the colomns are equally long
- The length of colomn S is calculated with the code:
lenght_columns = Range("S2").End(xlDown).Row
-lsb1 = listbox1


This is my code:
Private Sub CommandButton1_Click()
lenght_columns = Range("S2").End(xlDown).Row




For i = 1 To lenght_columns


Lsb1.ColumnCount = 22
Lsb1.AddItem
Lsb1.List(i - 1, 0) = Cells(i, 19)
Lsb1.AddItem
Lsb1.List(i - 1, 1) = Cells(i, 20)
Lsb1.AddItem
Lsb1.List(i - 1, 2) = Cells(i, 21)
Lsb1.AddItem
Lsb1.List(i - 1, 3) = Cells(i, 22)
Lsb1.AddItem
Lsb1.List(i - 1, 4) = Cells(i, 23)
Lsb1.AddItem
Lsb1.List(i - 1, 5) = Cells(i, 24)
Lsb1.AddItem
Lsb1.List(i - 1, 6) = Cells(i, 25)
Lsb1.AddItem
Lsb1.List(i - 1, 7) = Cells(i, 26)
Lsb1.AddItem
Lsb1.List(i - 1, 8) = Cells(i, 27)
Lsb1.AddItem
Lsb1.List(i - 1, 9) = Cells(i, 28)
Lsb1.AddItem
Lsb1.List(i - 1, 10) = Cells(i, 29)
Lsb1.AddItem
Lsb1.List(i - 1, 11) = Cells(i, 30)
Lsb1.AddItem
Lsb1.List(i - 1, 12) = Cells(i, 31)
Lsb1.AddItem
Lsb1.List(i - 1, 13) = Cells(i, 32)
Lsb1.AddItem
Lsb1.List(i - 1, 14) = Cells(i, 33)
Lsb1.AddItem
Lsb1.List(i - 1, 15) = Cells(i, 34)
Lsb1.AddItem
Lsb1.List(i - 1, 16) = Cells(i, 35)
Lsb1.AddItem
Lsb1.List(i - 1, 17) = Cells(i, 36)
Lsb1.AddItem
Lsb1.List(i - 1, 18) = Cells(i, 37)
Lsb1.AddItem
Lsb1.List(i - 1, 19) = Cells(i, 38)
Lsb1.AddItem
Lsb1.List(i - 1, 20) = Cells(i, 39)
Lsb1.AddItem
Lsb1.List(i - 1, 21) = Cells(i, 40)


Next i




End Sub

Thanks in advance!

[snb]

Never use additem to populate a listbox/combobox. Use .List instead.

e.g
Listbox1.list=sheet1.range("S1:AO20").value

See: http://www.snb-vba.eu/VBA_Fill_combobox_listbox.html

[TimdeBlaeij]

Hi snb,

First of all, thanks for the quick reply!
I tried using your code and it works perfectly except for one thing, the colomns. I can't seem to get multiple. What am I doing wrong?


I tried two things:


Private Sub CommandButton1_Click()
lenght_columns = Range("S2").End(xlDown).Row


Lsb1.ColumnCount = 2
Lsb1.List = Sheet1.Range("S2", Cells(lenght_columns, 19)).Value
Lsb1.List = Sheet1.Range("T2", Cells(lenght_columns, 20)).Value

End Sub



Private Sub CommandButton1_Click()
lenght_columns = Range("S2").End(xlDown).Row


Lsb1.ColumnCount = 2
Lsb1.List (1,0)= Sheet1.Range("S2", Cells(lenght_columns, 19)).Value
Lsb1.List (1,1)= Sheet1.Range("T2", Cells(lenght_columns, 20)).Value

End Sub

[mdmackillop]

Private Sub CommandButton1_Click()
Set r = Range("S1").CurrentRegion
Set r = r.Offset(1).Resize(r.Rows.Count - 1)
With LSB1
.ColumnCount = r.Columns.Count
.RowSource = r.Address
.ColumnHeads = True
End With
End Sub

[TimdeBlaeij]

Thank you so much!

[TimdeBlaeij]

How do I mark solved? :doh:

[mdmackillop]

Happy to help; see Thread tools above the first post.

[TimdeBlaeij]

Thanks, again :)

[snb]

Avoid .rowsource, use .List

[mdmackillop]

@snb
It appears if you want ColumnHeads you need to use RowSource.

[snb]

@mdmac

Listbox & combobox columnheads contain known bugs.
there's no need to link a listbox to a worksheet.

[TimdeBlaeij]

Is it possible to change the colomnwidths of the lisbox to the same width as the columns on the sheet?

[snb]

Why would you ?

[TimdeBlaeij]

Because the heading of my columns in my listbox aren't shown completely. It looks like this [artic][numbe][Suppli][amount stor]. each [] is a column heading

[JKwan]

.ColumnWidths = "100;20;50;50"

[mdmackillop]

Adjust Adj to suit

Private Sub CommandButton1_Click()
adj = 4
Set r = Range("S1").CurrentRegion
Set r = r.Offset(1).Resize(r.Rows.Count - 1)


For i = 1 To r.Columns.Count
x = x & ";" & r(1, i).ColumnWidth * adj
Next
x = Right(x, (Len(x) - 1))
With LSB1
.ColumnCount = r.Columns.Count
.RowSource = r.Address
.ColumnHeads = True
.ColumnWidths = x
End With
End Sub

[TimdeBlaeij]

Thanks!!

[snb]

Id use


Private Sub Userform_initialize()
With ListBox1
.List = Sheet2.Range("A1:E20").Value

.ColumnCount = UBound(.List, 2) + 1
For j = 0 To .ColumnCount - 1
c00 = c00 & ";" & Len(.List(0, j)) * 6
y = y + Len(.List(0, j)) * 6.5
Next
.ColumnWidths = Mid(c00, 2)
.Width = y
Width = y + .Left + 12
End With
End Sub