Reading all items from a Listbox into an array

[AndyGalloway]

Hi guys. This problem is driving me bananas. I have two list boxes. The first is populated with training subjects that are outstanding for an employee. Using buttons, some subjects are moved across to the second list box. Now, when I click on the OK button, I want all items in the second list box to be copied into an array called strSubject. strSubject() is defined publicly as a string. The question is, how do I copy the items in list box 2 into the array? I won't confuse the issue by posting my failed attempts to code this.

 

[Yongle]

at top of a standard module

Public strSubject() As String

You did not state which type of listboxes you are using
- code below tested with active-x listbox (code goes in sheet module) and userform listbox (code goes in userform module)

    Dim i As Long
    ReDim strSubject(ListBox2.ListCount - 1)
    For i = 0 To ListBox2.ListCount - 1
        strSubject(i) = ListBox2.List(i)
        Debug.Print strSubject(i)           'see output in immediate window
    Next

 

[JLGWhiz]

What type of list box are you using? This seems to work for Forms control listbox with single column list.

Sub t()
Dim ary As Variant
ary = Sheet1.Shapes("List Box 1").ControlFormat.List
MsgBox ary(UBound(ary))
End Sub

 

[AndyGalloway]

at top of a standard module

Public strSubject() As String

You did not state which type of listboxes you are using
- code below tested with active-x listbox (code goes in sheet module) and userform listbox (code goes in userform module)

    Dim i As Long
    ReDim strSubject(ListBox2.ListCount - 1)
    For i = 0 To ListBox2.ListCount - 1
        strSubject(i) = ListBox2.List(i)
        Debug.Print strSubject(i)           'see output in immediate window
    Next

This is exactly what I wanted. I got so close to coding this myself, but I could not find reference to how to reference an item in the listbox list i.e. ListBox2(i). Thank you for your help.

 

[Fluff]

Why not just use

MyArray=ListBox1.List

 

[Domenic]

Same method that JLGWhiz and Fluff have pointed out...

    'transfer the contents of listbox to variant array
    Dim listboxItems As Variant
    listboxItems = ListBox1.List
    
    'print items from listbox to Immediate Window
    Dim i As Long
    For i = LBound(listboxItems) To UBound(listboxItems)
        Debug.Print listboxItems(i, 0)
    Next i

 

[AndyGalloway]

Why not just use

MyArray=ListBox1.List

Thanks Fluff. This seems the most elegant solution and it's the one I have gone with. Thanks to everyone for their input. It's much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Yongle]

Why not just use

MyArray=ListBox1.List

Thanks Fluff. This seems the most elegant solution and it's the one I have gone with. Thanks to everyone for their input. It's much appreciated.

agreed