VBA: Doubleclick list box, identitfy values between commas and highlight corresponding identified values in another listbox

Question

Image of userform,listbox

I'm struggling to know how to even ask this.

I have a UserForm, with listbox1 showing all the inputted data from the username. In the UserForm, I have a listbox2 which allows multiple selections, puts a comma between them and sends to a column in listbox1.

What I want to do now is the opposite of the above. Double-click row from listbox1, so that values are populated in the UserForm, including listbox2.

How can I amend the below form to do this?

Listbox1: Where I will double-click Lisbox2: Homeroom (listbox with comma separated values) Userform: Data in userform to be populated upon double-clicking a row

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    txtSearch.Text = ListBox1.Column(1)
    
    If txtSearch.Text = ListBox1.Column(1) Then
    
        cmbGrade.Text = Me.ListBox1.Column(2)
        
        lstHomeroom = Me.ListBox1.Column(3)
              
        cmbSubjectCode.Text = Me.ListBox1.Column(4)
        
        cmbClassroom.Text = Me.ListBox1.Column(5)
        
        cmbNumberLessons.Text = Me.ListBox1.Column(7)
        
    End If

End Sub

Answer 1

Option Explicit

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim x As Integer, i As Integer, aName, y As Integer
    For x = 0 To ListBox1.ListCount - 1
        ' Get selected item
        If ListBox1.Selected(x) Then
            ' Split item by comma
            aName = Split(Me.ListBox1.Column(2, x), ",")
            For i = 0 To UBound(aName)
                For y = 0 To ListBox2.ListCount - 1
                    If Me.ListBox2.List(y) = aName(i) Then
                        ' Select an item of ListBox
                        Me.ListBox2.Selected(y) = True
                        Exit For
                    End If
                Next y
            Next
        End If
    Next x
End Sub