> hello,
>
[quoted text clipped - 19 lines]
> after If Bcell.Value = "C" Or ... and that it has to loop through the
> listbox, but i have no idea what the code has to be. Can any help me with it?
Hi
This is a classic problem we all come across eventually.
Add the items to a collection first, then add those to the list
Private Sub cmb1_Click()
myRow = ActiveCell.Row
Dim Bcell As Range
Dim ListCollection as New Collection, ListValue as Variant, item as
Variant
Form1.ListBox1.Clear
On error resume next
For Each Bcell In Range(("A" & myRow) & (":J" & myRow))
If Bcell.Value = "C" Or Bcell.Value = "S" Or Bcell.Value = "I"
Then
ListValue =
ThisWorkbook.Sheets("Sheet1").Range(((ConvertToLetter(Bcell.Column)) &
"1"))
ListCollection.Add ListValue, Cstr(ListValue)
End if
Next Bcell
on error goto 0
'now add to listbox
for each Item in ListCollection
Form1.ListBox1.AddItem Item
next item
end sub
The add in a collection creates an error if the item already exists,
and the copy is not added. The "on error" bits catch the errors.
Check John Walkenbach's website for more on this (like sorting the
list) - I think this was his idea first.
regards
Paul
Frank M - 16 Nov 2007 13:04 GMT
Thanks, this works exactly how i want it
> > hello,
> >
[quoted text clipped - 54 lines]
> regards
> Paul