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]

How many columns does the listbox have and what are their widths?

 

[devofish]

There are 3 columns (0;0;100 in both the FillListBox code and the listbox properties). I only need to see the EMAIL [column C] in the listbox. The other data is "hidden". ID is in column A.

 

[Norie]

If there are 3 columns why are you only populating one?

 

[devofish]

Apologies for not being clear and thank you for answering my post. After some testing and reworking, I believe I have gotten it to work properly with only one problem now. FillListBox sub is called on within initialization with 3 columns ([A] ID# @ 0 pt; Group @ 0 pt; [C] Email @ 200 pt). The ID and Group need to be hidden so I have the capability to update or delete a unique ID upon listbox selection, since there are many emails within any given group. Is the problem within combobox change? It is currently only saying to populate [C] into listindex 0, correct? I've tried a few different ways to get the ID and Group into the listbox with combobox selection. How could you write that to include the whole row? Please let me know what you think. Cheers.

 

[Norie]

You have a separate sub named FillListBox?

When are you calling that?

Can you post its code?

 

[devofish]

Note: When widths are 0;0;100, nothing displays in listbox upon combobox change, but textbox control shows Column C value is there confirmed when listbox is selected and value populates into control. At 100;0;100 Column C (only) is visible in the listbox and populates into text control correctly, albeit Column A (ID) does not populate into listbox and thus the textcontrol.

Sub FillListBox()   
    Dim lbtarget As MSForms.ListBox
    LastRow = Sheets(msSHEET_NAME).Range("A" & Rows.Count).End(xlUp).Row
    With Sheets(msSHEET_NAME)
        .Range("A2:C" & LastRow).Sort key1:=.Range("A2:A" & LastRow), _
        order1:=xlAscending, Header:=xlYes
        Set rng = .Range("A2:C" & LastRow)
        Set lbtarget = Me.ListBox1
        Me.ListBox1.List = rng.Value
    End With
    With lbtarget
        .ColumnCount = 3
        .ColumnWidths = "0;0;100"
        .List = rng.Cells.Value
    End With
End Sub

Private Sub UserForm_Initialize()
    cmdAdd.Enabled = True
    cmdDelete.Enabled = False
    cmdUpdate.Enabled = False
    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
    FillListBox
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.ListBox1.Clear
    cmdAdd.SetFocus
End Sub

Private Sub ComboBox1_Change()
    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
                frmEmail.ListBox1.AddItem Sheets(msSHEET_NAME).Cells(cRow, 3).Value
            End If
        cRow = cRow + 1
    Loop
End Sub

 

[Norie]

I'm confused, in the combobox change event this is the only code that populates the listbox.

If Sheets(msSHEET_NAME).Cells(cRow, 2).Value = frmEmail.ComboBox1.Value Then
                frmEmail.ListBox1.AddItem Sheets(msSHEET_NAME).Cells(cRow, 3).Value
            End If

That is only going to populate the first column of the listboxx.

 

[devofish]

I think that's where my problem is. Apologies for the confusion. I need to populate all 3 columns upon change event, but I'm not familiar with how to incorp that into the loop...or if I need a For Next instead.

 

[devofish]

Is there a better way to populate all row data when a combobox selection is made?