VBA add more columns to ListBox

Question

I want to add more columns to Listbox Have to change somwthing in RowSource?

If is something with to put array, but im not doing it

Sub preencherListBox()
Dim ultimaLinha As Long
Dim linha As Integer
'retorna ao valor ultima linha preenchida
ultimaLinha = Folha1.Range("A100000").End(xlUp).Row
'percorre da segunda linha até a última linha e atribui o valor da primeira e segunda coluna
For linha = 2 To ultimaLinha
   Visualizar.ListBox1.AddItem Folha1.Range("A" & linha)
   Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 1) = Folha1.Range("B" & linha)
   Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 2) = Folha1.Range("C" & linha)
   Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 3) = Folha1.Range("D" & linha)
   Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 4) = Folha1.Range("E" & linha)
   Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 5) = Folha1.Range("F" & linha)
   Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 6) = Folha1.Range("G" & linha)
   Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 7) = Folha1.Range("H" & linha)
   Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 8) = Folha1.Range("I" & linha)
   Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 9) = Folha1.Range("J" & linha)
   'Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 10) = Folha1.Range("K" & linha)
   'Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 11) = Folha1.Range("L" & linha)
   'Visualizar.ListBox1.List(Visualizar.ListBox1.ListCount - 1, 12) = Folha1.Range("M" & linha)
Next

End Sub

You can't use AddItem if the listbox has more than 9 columns, put the data for the listbox into an array and use List instead. — norie
– norie, Commented Oct 5, 2021 at 18:01
@HugTheCode Did you find an answer to your question? You got two of them, feel free to mark the preferred one as accepted by ticking the green checkmark. — T.M.
– T.M., Commented Oct 20, 2021 at 17:50

norie · Accepted Answer · 2021-10-05 18:05:41Z

2

You can't use AddItem if the listbox has more than 9 columns, put the data for the listbox into an array and use List instead.

Option Explicit

Sub preencherListBox()
Dim arrData As Variant
Dim ultimaLinha As Long
Dim linha As Integer

    'retorna ao valor ultima linha preenchida
    ultimaLinha = folha1.Range("A100000").End(xlUp).Row

    arrData = folha1.Range("A2:M" & ultimaLinha).Value
    
    With Visualizar.ListBox1
        .ColumnCount = 13
        .List = arrData
    End With
    
End Sub

answered Oct 5, 2021 at 18:05

norie

9,9372 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

T.M. Over a year ago

Fyi Posted some additions to your answer which shows the right direction if used without binding to a RowSource +:) @norie

T.M. · Accepted Answer · 2021-10-06 07:50:42Z

A) Generally

a (listbox) control bound to data doesn't accept the AddItem method, i.e. AddItem fails raising a run-time error 70.

So it will be necessary to decide between

a) changing the RowSource property either manually or via code assignment or
b) assignments to the array list property either via array or AddItem.

B) If used without bound RowSource, @Norie 's statement (though leading to the right direction) needs some additions

"You can't use AddItem if the listbox has more than 9 columns."

It's true that by default the use of AddItem provides only for 10 columns (with zero-based list column indexes ranging from 0 to 9).
Note that .AddItem looping through each cell in a given range is time consuming compared to assigning an entire datafield array to the listbox'es .List property.
Initializing the listbox via an empty list assignment plus eventual row deletion would actually enable a later AddItem execution to fill more than 10 columns:

Proof

Private Sub UserForm_Initialize()
    With Me.ListBox1
        ReDim tmp(0 To 0, 0 To 11)
        .List = tmp
        .ColumnCount = 12
        .ColumnWidths = "30;30;;;;;;;;40;41;42"
    .RemoveItem 0
        
        .AddItem "one"
        .List(.ListCount - 1, 1) = "two"
        .List(.ListCount - 1, 10) = "eleven"
        .List(.ListCount - 1, 11) = "twelve"
    End With
End Sub

Collectives™ on Stack Overflow

VBA add more columns to ListBox

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related