Populate listbox values from combobox selection

[devofish]

I know this is pretty simple. I have a form with 2 textboxes (ID"ColA", Email"ColC") and a combobox (Group"ColB") I want to populate a listbox items with a combobox change event. Everytime the change event fires, the listbox goes blank but I can still select the row data in the listbox, however the text controls from the listbox click are displaying the wrong indexes. If I just fill the listbox and take out the change event, the textbox controls have the correct indexes. Below is what I have so far:

Private Sub UserForm_Initialize()
    With Sheets(msSHEET_NAME)
        LastRow = .Range("B" & Rows.Count).End(xlUp).Row
        For j = 2 To LastRow
            If Application.CountIf(.Range("B2:B" & j), .Range("B" & j)) = 1 Then
                ComboBox1.AddItem .Range("B" & j).Value
            End If
        Next
    End With
[COLOR=#008000]    'FillListBox '\\test...should be taken out so listbox is cleared upon form initialization[/COLOR]
    With Sheets(msSHEET_NAME)
        Me.TextBox1.Value = ""
        Me.TextBox2.Value = ""
        Me.ListBox1.Clear
    End With
End Sub

Private Sub ComboBox1_Change()
    Me.ListBox1.Clear [COLOR=#008000]'\\have taken out and left in with no apparent effect[/COLOR]
    cRow = 2
    Do Until Sheets(msSHEET_NAME).Cells(cRow, 2).Value = Empty
            If Sheets(msSHEET_NAME).Cells(cRow, 2).Value = frmEmail.ComboBox1.Value Then
                frmEmail.ListBox1.AddItem Sheets(msSHEET_NAME).Cells(cRow, 3).Value
            End If
        cRow = cRow + 1
    Loop
End Sub

Just for sake of completion below is my click event:

Private Sub ListBox1_Click()    
With Sheets(msSHEET_NAME)
        TextBox1.Value = Me.ListBox1.List(ListBox1.ListIndex, 2) '\\populates email textbox for editing
        TextBox2.Value = Me.ListBox1.List(ListBox1.ListIndex, 0) '\\populates ID textbox (hidden) used for deletion and updating referencing
    End With
End Sub

 

[Norie]

Which columns from the worksheet go in which columns in the listbox?

As it is, using AddItem, your code is putting values from column C on the sheet into column 1 of the listbox.

 

[devofish]

ws A = 'listbox column 1
ws B = 'listbox column 2
ws C = 'listbox column 3

 

[Norie]

Try this.

Private Sub ComboBox1_Change()
Dim I As Long

    Me.ListBox1.Clear

    cRow = 2

    Do Until Sheets(msSHEET_NAME).Cells(cRow, 2).Value = Empty
        If Sheets(msSHEET_NAME).Cells(cRow, 2).Value = frmEmail.ComboBox1.Value Then
            With frmEmail.ListBox1
                .AddItem
                For I = 0 To 2
                    .List(.ListCount - 1, I).Value = Sheets(msSHEET_NAME).Cells(cRow, I + 1).Value
                Next I
            End With

        End If
        cRow = cRow + 1
    Loop

End Sub

 

[devofish]

Thank you Norie. That seems to work.