How to store all selected data from listbox in an array in VBA

Question

I would like to know if there is a simple way to store all selected item from a ListBox into an array.

I tried to do this with the following code, but it didn't work has excepted. FilterTest() return me nothing.

Private Sub ListBox1_Change()

    Dim FilterTest() As Variant
    Dim myMsg As String
    Dim i As Long
    Dim Count As Integer


    Count = 1

    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then

            ReDim Preserve FilterTest(Count)
            FilterTest(Count) = ListBox1.List(i)
            Count = Count + 1


        End If
    Next i

End Sub

Tim Stack · Accepted Answer · 2019-06-14 12:36:16Z

2

You were close. Arrays are base 0 collections (unless specified otherwise) so start counting with 0. You can also drop the parentheses when declaring the variable. Next, redim the variable the first time and then redim preserve it the following iterations.

Private Sub ListBox1_Change()

    Dim FilterTest As Variant
    Dim myMsg As String
    Dim i As Long
    Dim Count As Integer

    Count = 0

    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            If Count = 0 Then
                Redim FilterTest(Count)
            Else
                Redim Preserve FilterTest(Count)
            End If
            FilterTest(Count) = ListBox1.List(i)
            Count = Count + 1
        End If
    Next i

End Sub

answered Jun 14, 2019 at 12:36

Tim Stack

3,2483 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Tim Stack Over a year ago

@Dorian what do you mean with "these values"? Please clarify

TourEiffel Over a year ago

Thank you @Tim Stack can you please explain me why with Count = 1 this don't work ? ;). I just found the way to do this thats why i deleted the com sorry

Tim Stack Over a year ago

Because 1 is actually the second item of an array. It's a base 0 collection, so 0 is actually the first item

TourEiffel Over a year ago

How can I use the data stored into the array ? When i try to Print all data selected into a message box out of the Sub I got the Error Code 9 : Out of range. For example why does this command line don't work ? MsgBox FilterTest(1) I was thinking that this would display the second element of the array..

Tim Stack Over a year ago

I am afraid I can't help you debug the code without seeing the code. I think that at this point it's best to start a new question as these problems are unrelated to the primary question. It would help to first read about arrays here

|

Collectives™ on Stack Overflow

How to store all selected data from listbox in an array in VBA

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related