Multiple Listboxes into single array 1

[stevio]

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

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

 

[Andrzejek]

How many other listboxes are you going to use?

If just one more, then you can simply do:

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[green]
    'check if the row is selected and add to count[/green]
    If ListBox1.Selected(i) Then Count = Count + 1
Next i
[blue]
For i = 0 To ListBox2.ListCount - 1
    If ListBox2.Selected(i) Then Count = Count + 1
Next i
[/blue][green]
'based on the above count declare the array[/green]
ReDim myArray(Count)

j = 0
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then[green]
        'if selected then store the item from the
        'first column in the array. change 1 to the
        'respective column number[/green]
        myArray(j) = ListBox1.List(i, 0)
        j = j + 1
    End If
Next i
[blue]
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
[/blue][green]
'Check values stored in array[/green]
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.

 

[stevio]

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

 

[Andrzejek]

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.

 

[combo]

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

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":

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

 

[Andrzejek]

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

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[green]
        'If ctl.Tag = "CountMeIn" Then
            'in case you want to only detect listboxes
            'with the Tag "CountMeIn"[/green]
            For i = 0 To ctl.ListCount - 1
                If ctl.Selected(i) Then Count = Count + 1
            Next i[green]
        'End If[/green]
    End If
Next ctl
[green]
'based on the above count declare the array[/green]
ReDim myArray(Count)

j = 0
For Each ctl In Me.Controls
    If TypeOf ctl Is MSForms.ListBox Then[green]
        'If ctl.Tag = "CountMeIn" Then[/green]
            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[green]
        'End If[/green]
    End If
Next ctl
[green]
'Check values stored in array[/green]
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.