vba for Linking the Listboxes in userform and accessing through button

Question

I have created an userform with two Listboxes having Multiple option.

The Listbox1, is populated with the Location 1 of column L of sheet Result.
The Listbox2, is populated with the Location 2 of column M of sheet Result.

I have an Button designed as "GO!!"

Right now, I have a code, that works in such a way that, you click on the Listbox 1 (eg: Germany is Location1 , it filters for location ) Similarly, If you chose Listbox2 (eg.USA in location 2, it filters for location)

I would like to have a code, such a way that the two listboxes should be interlinked and accessed through button.

For Eg. If I click "USA" from Listbox1. "Germany" from Listbox2, I Click "GO", It should show me the results of USA;with Germany in my sheet2.

Also,If I am not selecting the Listbox2, then it should display the result only for listbox1 and viceversa.

Here is the code, I am using for listbox1. I change the Listbox name for second listbox and the column number with the same code.

Private Sub DoFilter1()
    Dim ws As Worksheet
    Dim strCriteria() As String, i As Integer, arrIdx As Integer
    ReDim Preserve strCriteria(0 To arrIdx)

    arrIdx = 0
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            ReDim Preserve strCriteria(0 To arrIdx)
            strCriteria(arrIdx) = Me.ListBox1.List(i)
            arrIdx = arrIdx + 1
        End If
    Next i

    Set ws = Sheets("Result")
    If arrIdx = 0 Then
        ws.UsedRange.AutoFilter
    Else
        ws.Range("A:P").AutoFilter Field:=12, Criteria1:=Array(strCriteria), Operator:=xlFilterValues
    End If
End Sub

Could some one suggest me , how I could interlink them and access through buttons. Any lead would be helpful.

This is how my userform looks like. I would like to select the checkbox from the list and then click Ok to show the result. Also, I should be able to select more than one option in checkboxes.

nishit dey nishit dey · Accepted Answer · 2017-07-21T09:40:20

Please try the below code, This might be helpful. Thanks

        With ListBox1
            For x = 0 To .ListCount - 1
                If .Selected(x) Then
                    temp = temp & Chr(10) & .List(x)
                End If
            Next
        End With

        With ListBox2
            For x = 0 To .ListCount - 1
                If .Selected(x) Then
                    temp = temp & Chr(10) & .List(x)
                End If
            Next
        End With

        MsgBox temp & " is selected"

vba for Linking the Listboxes in userform and accessing through button

1 Answers