if cell value 1 then listbox .listindex = value

Question

I have the following code:

If ws.cells(A,B).value = "1" then .ListBox1.List(.ListBox1.ListIndex,5) = Checkbox.name

but it prompts an error and i'm not sure how to fix it

essentially, what i'm trying to achieve is that if the cell (A,B) has a value of 1 then insert a name of the checkbox into listbox, listindex of 5.

all other previous listindexes are added by the following code:

.list(.listcount -1, 1) = ws.cells(C,D).value

.list(.listcount -1,1) = ws.cells (E,F).value

Could you include more of your code? Also please include the specific error message. — David Zemens, Apr 10 '14 at 02:00
Hi, I've answered one of your questions earlier [HERE](http://stackoverflow.com/questions/22976383/whats-the-meaning-of-listcount-1/22977404#22977404). Don't be confused with adding an item to a `ListBox` and populating `Columns` of an existing `Item` in a `multi-colum ListBox`. — L42, Apr 10 '14 at 03:02

L42 · Accepted Answer · 2014-04-10T04:14:21.977

This is how you populate a ListBox with items.

For i = 1 to 5
    With me.ListBox1
        .AddItem i
    End With
Next

So assuming you have ListBox1 in UserForm1, this will result to:

Let's say you want to replace 5 by 6, you use .List property like this:

Me.ListBox1.List(4, 0) = 6

Syntax: expression.List(pvargIndex, pvargColumn)
Again, when you use .List property, Index and Column is always 1 less the actual Item and Column.
Since we want to replace 5 by 6 in a ListBox with 1 Column and 5 items in it, the Index of 5 is 4 and it's Column is 0.
The result would be:

Now, what if you have multiple-column ListBox and you want to populate those Columns.
Say Column 2 of our ListBox1, we use this code.

For i = 1 To 5
    With Me.ListBox1
        .List(i - 1, 1) = "Item" & i
    End With
Next

Take note of the Index which is i - 1 and the Column which is 1 (actual column count -1) The result would be:

So let's go back to your question.
This line:

If ws.cells(A,B).value = "1" then .ListBox1.List(.ListBox1.ListIndex,5) = Checkbox.name

will fail since you are trying to populate Column 6 of your ListBox1 which I believe doesn't exist.
Also you used .ListIndex property which returns the currently selected item index.
I don't know where you are executing your code but initially, .ListIndex value is -1 since it will only have value on runtime when the user get to select an Item.

Now these lines:

.list(.listcount -1, 1) = ws.cells(C,D).value
.list(.listcount -1, 1) = ws.cells(E,F).value

actually just replaces the last item second column value.
First it passes ws.cells(C,D).Value to it then overwrites it with ws.cells(E,F).value.
Hope this anwers some of your questions a bit.

if cell value 1 then listbox .listindex = value

1 Answers1