INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Multiple Listboxes into single array

Multiple Listboxes into single array

(OP)
Hi There,

Is it possible to add multiple listbox items into a single array? The code below works for a single Listbox1, how do I add a second Listbox to the end of the first array, e.g. Listbox1

CODE -->

Private Sub CmdOk_Click()
Dim myArray() As String
Dim Count As Integer, i As Integer, j As Integer
Count = 0

For i = 0 To ListBox1.ListCount - 1
    'check if the row is selected and add to count
    If ListBox1.Selected(i) Then Count = Count + 1
Next i

'based on the above count declare the array
ReDim myArray(Count)

j = 0
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        'if selected then store the item from the
        'first column in the array. change 1 to the
        'respective column number
        myArray(j) = ListBox1.List(i, 0)
        j = j + 1
    End If
Next i

'Check values stored in array
For i = 0 To Count - 1

    MsgBox myArray(i)

Next i

End Sub 

RE: Multiple Listboxes into single array

How many other listboxes are you going to use?

If just one more, then you can simply do:

CODE

Private Sub CmdOk_Click()
Dim myArray() As String
Dim Count As Integer, i As Integer, j As Integer
Count = 0

For i = 0 To ListBox1.ListCount - 1
    'check if the row is selected and add to count
    If ListBox1.Selected(i) Then Count = Count + 1
Next i

For i = 0 To ListBox2.ListCount - 1
    If ListBox2.Selected(i) Then Count = Count + 1
Next i

'based on the above count declare the array
ReDim myArray(Count)

j = 0
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        'if selected then store the item from the
        'first column in the array. change 1 to the
        'respective column number
        myArray(j) = ListBox1.List(i, 0)
        j = j + 1
    End If
Next i

For i = 0 To ListBox2.ListCount - 1
    If ListBox2.Selected(i) Then
        myArray(j) = ListBox2.List(i, 0)
        j = j + 1
    End If
Next i

'Check values stored in array
For i = 0 To Count - 1
    MsgBox myArray(i)
Next i

End Sub 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Multiple Listboxes into single array

(OP)
Thank you Andy

Could be multiple - let's say it was up to 10 listboxes, is there an easy way to "compact" the code?

Thanks again
Stevio

RE: Multiple Listboxes into single array

Yes, there is a way.
You can loop through controls in UserForm, detect which ones are listboxes, and loop through items in each listbox. You can do that to 1, 2, 10, or 100 listboxes, same code.

There are just two lines of code difference if you want to loop thru all listboxes on your Form, or just some (not all) listboxes on your Form. Hint - use a Tag property to do so.

Give it a try, post back your code if you get stuck.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Multiple Listboxes into single array

You could encapsulate in function resizing array based on listbox selection:

CODE -->

Function FillMyArray(inArray() As String, inListBox As MSForms.ListBox) As String()
Dim tmpArray() As String
tmpArray = inArray
For i = 0 To inListBox.ListCount - 1
    If inListBox.Selected(i) Then
        On Error Resume Next
        j = UBound(tmpArray)
        If Err.Number > 0 Then
            j = -1
            Err.Clear
        End If
        On Error GoTo 0
        j = j + 1
        ReDim Preserve tmpArray(j)
        tmpArray(j) = inListBox.List(i, 0)
    End If
Next i
FillMyArray = tmpArray
End Function 
This function can be called depending on the logic in your code, for all listboxes with names starting with "List":

CODE -->

Dim myArray() As String
Dim ctl As MSForms.Control
For Each ctl In Me.Controls
    If (TypeOf ctl Is MSForms.ListBox) And ctl.Name Like "List*" Then myArray = FillMyArray(myArray, ctl)
Next ctl 

combo

RE: Multiple Listboxes into single array

I am all for reusable code, and combo's example does that.
But I would start simple and just do this:

CODE

Private Sub cmdOK_Click()
Dim myArray() As String
Dim Count As Integer, i As Integer, j As Integer
Dim ctl As MSForms.Control

Count = 0

For Each ctl In Me.Controls
    If TypeOf ctl Is MSForms.ListBox Then
        'If ctl.Tag = "CountMeIn" Then
            'in case you want to only detect listboxes
            'with the Tag "CountMeIn"
            For i = 0 To ctl.ListCount - 1
                If ctl.Selected(i) Then Count = Count + 1
            Next i
        'End If
    End If
Next ctl

'based on the above count declare the array
ReDim myArray(Count)

j = 0
For Each ctl In Me.Controls
    If TypeOf ctl Is MSForms.ListBox Then
        'If ctl.Tag = "CountMeIn" Then
            For i = 0 To ctl.ListCount - 1
                If ctl.Selected(i) Then
                    myArray(j) = ctl.List(i, 0)
                    j = j + 1
                End If
            Next i
        'End If
    End If
Next ctl

'Check values stored in array
For i = 0 To Count - 1
    MsgBox myArray(i)
Next i

End Sub 

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close