Add Cell Values By Criteria To Listbox

nb- · Dec 19th 2007

Is it possible to add items to a listbox as selected without using a counter?

what I have is a multiselect listbox, and I am adding items and selecting them using a counter (see below), but is there a more cunning/succinct way of adding the items as selected? I keep thinking there should be...

Code

...
With ListBox1
    .AddItem "Sample1"
    .AddItem "Sample2"
    .AddItem ""
    iCount = 2
   For Each CurCell In Worksheets(datasheet).Range(tempstring)
   iCount = iCount + 1
    
    If CurCell.Offset(0, 2).Value = 1 Then
        .AddItem CurCell.Value
        ListBox1.Selected(iCount) = True
    Else
        .AddItem CurCell.Value
    End If
    
   Next
End With
...

Display More

**mikerickson** · Dec 19th 2007

Re: Add Listbox Items As Selected Without Counter

Replace your current .Selected line with

Code

.Selected(.ListCount-1) = True

and you won't need the counter variable.

nb- · Dec 19th 2007

Re: Add Listbox Items As Selected Without Counter

Thanks, .ListCount was what I was evidently after.

I now have;

Code

...
With ListBox1
    .AddItem "Sample1"
    .AddItem "Sample2"
    .AddItem ""
   For Each CurCell In Worksheets(datasheet).Range(tempstring)
   .AddItem CurCell.Value
    If CurCell.Offset(0, 2).Value = 1 Then
        .Selected(.ListCount - 1) = True
    End If    
   Next
End With
...

Display More

**norie** · Dec 19th 2007

Re: Add Listbox Items As Selected Without Counter

nb

There is no way to avoid using a loop with a multiselect listbox in Excel.

Is it really a problem anyway?

Add Cell Values By Criteria To Listbox

Participate now!

Share