Add items to listbox

Question

Good afternoon, you guys

It’s a very stupid question, because I’m starting in vba programming and I’m not being able to correct this error. I’d appreciate it if someone could help me I would like to fill a listbox with three columns in which the column title is : Criterion, Distribution and Variation The first column shall be filled with E1, E2, E3, ... E10 (10 lines) The second column shall be filled in with the text "uniform" for all rows The third column shall be filled with the value "10" for all rows

However, I am only able to assign the value of the title of the first column. When I try to add the title of the second one the following error is displayed: Could not set List property. Invalid property array index. vba

The Columncount property was set to 3

the code I was trying was as follows:

    With Me.lstbxCritEst
    .AddItem
    .List(0, 0) = "Critério"
    .List(1, 0) = "Distribuição"  ' o erro ocorre nessa linha
    .List(2, 0) = "Variação"
  End With

Answer 1

First of all, your question is not stupid.

Secondly, your code was mislabeled in ". List(1, 0) = "Distribution", because the line and column positioning in . List is inverted (right is (0.1)).

Third, what is missing from your code is a repeat structure that goes through all the filled lines in the spreadsheet and takes these values to the listbox.

As a first step, in the Listbox properties, since it will need to have three columns, it is interesting to fill with "3" in the "Columncount" field":

Already my spreadsheet, I assembled as follows, considering the information you provided:

Finally, my code suggestion is as follows, which I inserted into an Activex button:

Private Sub btExecutaII_Click()

'Declara as variáveis
Dim W       As Worksheet
Dim UltCel  As Range
Dim A       As Integer
Dim Ln      As Integer

'Atribui à variável W o nome da aba da planilha (Lista, nesse caso)
Set W = Sheets("Lista")

'Seleciona a a aba Lista e, em seguida, seleciona a célula A1
W.Select
W.Range("A1").Select

'Atribui à variável UltCel o valor da última linha preenchida na coluna A
Set UltCel = W.Cells(W.Rows.Count, 1).End(xlUp)

'Limpa a ListBox, para qual eu atribuí o nome de Listagem (o nome padrão é ListBox1)
Listagem.Clear

'Atribui os valores 0 e 1 às variaveis A e Ln, respectivamente
A = 0
Ln = 1

'Cria uma estrutura de repetição para preencher a ListBox com os valores de cada linha preenchida nas colunas A,B e C
Do While A <= UltCel.Row

    With Listagem
    
        .AddItem
        .List(A, 0) = W.Cells(Ln, 1).Value
        .List(A, 1) = W.Cells(Ln, 2).Value
        .List(A, 2) = W.Cells(Ln, 3).Value
    
    End With
    
    Ln = Ln + 1
    A = A + 1
    
Loop

'Esvazia as variáveis A, Ln e UltCel
A = Empty
Ln = Empty
UltCel = Empty

MsgBox "Pronto!", vbOKOnly, "Status"



End Sub

The expected result is this one:

Any questions or problems, just ask here.

I hope it helps!