Lisbox filters on combobox selection

DebugMe · Jun 9th 2020

Hi,

It's the VBA noob again

I have made a userform for data imput with a listbox (10 columns: A:H) showing all the data in sheet1, but i have tried to change this so the listbox only show the data matching the selected name in comboboxName (column A in sheet1). But now my listbox show all in one column. I guess i don't use .addItem rigth?

Can anyone help me on this issue?

Code

Private Sub ComboBoxName_Change()
Me.ListBoxDataDispl.Clear
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:H"))
If Sheet1.Cells(i, "A").Value = Me.ComboBoxName.Value Then
Me.ListBoxDataDispl.AddItem Sheet1.Cells(i, 1).Value
Me.ListBoxDataDispl.AddItem Sheet1.Cells(i, 2).Value
Me.ListBoxDataDispl.AddItem Sheet1.Cells(i, 3).Value
Me.ListBoxDataDispl.AddItem Sheet1.Cells(i, 4).Value
Me.ListBoxDataDispl.AddItem Sheet1.Cells(i, 5).Value
Me.ListBoxDataDispl.AddItem Sheet1.Cells(i, 6).Value
Me.ListBoxDataDispl.AddItem Sheet1.Cells(i, 7).Value
Me.ListBoxDataDispl.AddItem Sheet1.Cells(i, 8).Value
Me.ListBoxDataDispl.AddItem Sheet1.Cells(i, 9).Value
Me.ListBoxDataDispl.AddItem Sheet1.Cells(i, 10).Value
End If
Next i
End Sub

Display More

royUK · Jun 9th 2020

Without seeing your workbook it's difficult to help.

Your code uses a Loop but AutoFilter is the best way to do this, look at this example and try to adapt it to your needs

DebugMe · Jun 10th 2020

Hmm.. i cant seem to get it to work right

royUK · Jun 10th 2020

It's nothing like my example.

Lisbox filters on combobox selection

Share

Tags