Adding Collections To ListBoxes

[k209310]

Hello

Is it possible to add a VBA collection to a list box?

Thanks

Chris

 

[Andrew Poulsom]

If you have created the collection you can loop around it adding the items, like this:

Dim Item As Variant
For Each Item In MyCollection
    ListBox1.AddItem Item
Next Item

Replace MyCollection with your variable.

 

[k209310]

Thanks

Would it also be possible to add the collection to one string or an array?

 

[Andrew Poulsom]

I'm not sure what you mean. What is in your collection and what do you want to do with it?

 

[k209310]

Im looping through an access table to read company names. When needed i add the names to collection. I then add the collection to a list box on a form in excel using the code you kindly provided.

However i also have to produce a list of the names that appear on one or two lines that are seperate by the ';' character for use in third party software. ie Name1;Name2;Name3 etc.

Does this make sense?

 

[Andrew Poulsom]

I suppose:

Dim Item As Variant 
Dim MyStr As String
MyStr = ""
For Each Item In MyCollection 
    If MyStr = "" Then
        MyStr = MyStr & Item
    Else
        MyStr = MyStr & ";" & Item
    End If
Next Item

 

[k209310]

Thanks Andrew. That is perfect.