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
CodePudding user response:
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
CodePudding user response:
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 manually or via code assignment or b) an array list assignment.
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