CODEWITHSUNDEEP
excelvba
HugTheCode
HugTheCode

Reputation: 1

VBA add more columns to ListBox

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

Upvotes: 0

Views: 1058

Answers (2)

T.M.
T.M.

Reputation: 9948

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

Upvotes: 0

norie
norie

Reputation: 9857

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

Upvotes: 2

Related Questions