Reading all items from a Listbox into an array

AndyGalloway

New Member
Joined
Apr 24, 2019
Messages
49
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.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,011
Office Version
365
Platform
Windows
at top of a standard module
VBA Code:
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)
VBA Code:
    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

Well-known Member
Joined
Feb 7, 2012
Messages
11,926
Office Version
2013
Platform
Windows
What type of list box are you using? This seems to work for Forms control listbox with single column list.
Code:
Sub t()
Dim ary As Variant
ary = Sheet1.Shapes("List Box 1").ControlFormat.List
MsgBox ary(UBound(ary))
End Sub
 

AndyGalloway

New Member
Joined
Apr 24, 2019
Messages
49
at top of a standard module
VBA Code:
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)
VBA Code:
    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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,628
Office Version
365
Platform
Windows
Why not just use
VBA Code:
MyArray=ListBox1.List
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,165
Same method that JLGWhiz and Fluff have pointed out...

VBA Code:
    '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

New Member
Joined
Apr 24, 2019
Messages
49
Why not just use
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,628
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,045
Messages
5,466,219
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top