For loop with multiple listboxes

Question

I pulled the code from a Mr. Excel post and tried to repurpose it for multiple listboxes.

I want the data entered to go across multiple rows based on different variables.
That is for it to make a row of the various combinations that are selected under the listbox.

Image link: https://i.sstatic.net/WFpCq.jpg
I'd like the options in Screenshot 1 to return Screenshot 2, but it returns Screenshot 3.
I'd like the options in Screenshot 4 to return Screenshot 5, but it returns Screenshot 6.

Private Sub CommandButton1_Click()

    Dim rng As Range
    Dim i As Long
    Dim A As Long
    
    Set rng = Range("A" & Rows.Count).End(xlUp).Offset(1)
    
    For A = 0 To ListBox2.ListCount - 1

        If ListBox2.Selected(A) = True Then
            rng.Resize(, 5).Value = Array(TextBox1.Value, TextBox2.Value, TextBox3.Value, ListBox2.List(A))
            Set rng = rng.Offset(1)
        End If

        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) = True Then
                rng.Resize(, 5).Value = Array(TextBox1.Value, TextBox2.Value, TextBox3.Value, ListBox1.List(i), ListBox2.List(A))
                Set rng = rng.Offset(1)
            End If
        Next i

    Next A

End Sub


Private Sub UserForm_Initialize()

    With ListBox1
        .List = Array("A", "B", "C")
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
    End With

    With ListBox2
        .List = Array("Kappa", "Keepo")
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
    End With

End Sub

Where am I going wrong, is it the Syntax or the entire approach?
How could do this for multiple listboxes, maybe even 4?

CDP1802 · Accepted Answer · 2021-04-07 20:21:27Z

1

You need nested loops (untested)

Private Sub CommandButton1_Click()

    Dim rng As Range,  i As Long, j  As Long, ar 

    Set rng = Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 5)
    
    ar = Array(TextBox1.Value,TextBox2.Value,TextBox3.Value,"","")

    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            ar(3) = ListBox1.List(i)

            For j = 0 To ListBox2.ListCount - 1
                If ListBox2.Selected(j) = True Then
                    ar(4) = ListBox2.List(j)

                    rng.Value = ar
                    Set rng = rng.Offset(1)

                End If
            Next
        End If
    Next
End Sub

answered Apr 7, 2021 at 20:21

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

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

1 Comment

KevinKevin Over a year ago

Thank you for your help lord CDP1802. I need to read up on nested loops! Is there anything you recommend to check out for learning basics of VBA?

Collectives™ on Stack Overflow

For loop with multiple listboxes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related