changing Listbox based on an other combobox

[Aranell]

Hi everybody I hope you can help me out with my small issue.
I have a combobox with 5 item/category and I have a multi column ListBox which take it resources from an extra excel file.
Know I need to change the ListBox based on my selection in the combobox.
Example I have
My Combobox named "CategoryComboBox" with 5 Categories : "All", "Corporate", "Student", "Others" and "Test"
And I have a multi column ListBox which like I said have it source from an excel sheet with Column A named "Category", Column B named "Names" and Column C named "Email"
I want when I select in my combobox "All" that I get everything in my ListBox (which work already pretty well)
When I select "Student" for example in my Combobox that only the Names and email in my ListBox would be highlighted who have "Student" in their Category (in Column A).
This is the code I did. Which work just fine. The only thing it shows me always just one name at the end result
Example I have in my excel file 3 names who have in their category column "Student".
But after I select student in my combobox only one name shows at end result in the listbox.

Here is my code:

Private Sub CategoryComboBox_Change()
Dim sDateiKundenListe As String, wbKundenListe As Workbook
Dim rng As Range, strFirst As String

CustomerListBox.Clear
sDateiKundenListe = "D:\Documents\ndalj25\Desktop\CustomerDB.xlsx"
If CategoryComboBox.Value = "All" Then
    Application.ScreenUpdating = False
    Application.StatusBar = "Loading Customer Address"
    Set wbKundenListe = Application.Workbooks.Open(Filename:=sDateiKundenListe, ReadOnly:=True)
    With wbKundenListe.Worksheets(1)
    CustomerName = .Range(.Cells(2, 1), .Cells.SpecialCells(xlCellTypeLastCell))
    End With
    wbKundenListe.Close savechanges:=False
    Application.ScreenUpdating = True
    Application.StatusBar = False
    CustomerListBox.List = CustomerName
End If
If CategoryComboBox.Value = "Corporate" Then
    
    Application.ScreenUpdating = False
    Application.StatusBar = "Loading Customer Address"
    Set wbKundenListe = Application.Workbooks.Open(Filename:=sDateiKundenListe, ReadOnly:=True)
    With wbKundenListe.Worksheets(1)
        Set rng = .Columns(9).Find(What:="Corporate", LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False, After:=Range("I1"))
        If Not rng Is Nothing Then
            strFirst = rng.Address
            Do
            CustomerName = .Range(.Cells(rng.Row, 1), .Cells(rng.Row, 11))
            CustomerListBox.List = CustomerName
        Set rng = .Columns(9).FindNext(rng)
        Loop While Not rng Is Nothing And strFirst <> rng.Address
        End If
    End With
    wbKundenListe.Close savechanges:=False
    Application.ScreenUpdating = True
    Application.StatusBar = False
End If
If CategoryComboBox.Value = "Student" Then
    
    Application.ScreenUpdating = False
    Application.StatusBar = "Loading Customer Address"
    Set wbKundenListe = Application.Workbooks.Open(Filename:=sDateiKundenListe, ReadOnly:=True)
    With wbKundenListe.Worksheets(1)
        Set rng = .Columns(9).Find(What:="Student", LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False, After:=Range("I1"))
        If Not rng Is Nothing Then
            strFirst = rng.Address
            Do
            CustomerName = .Range(.Cells(rng.Row, 1), .Cells(rng.Row, 11))
            CustomerListBox.List = CustomerName
        Set rng = .Columns(9).FindNext(rng)
        Loop While Not rng Is Nothing And strFirst <> rng.Address
        End If
    End With
    wbKundenListe.Close savechanges:=False
    Application.ScreenUpdating = True
    Application.StatusBar = False
End If
If CategoryComboBox.Value = "Others" Then
    
    Application.ScreenUpdating = False
    Application.StatusBar = "Loading Customer Address"
    Set wbKundenListe = Application.Workbooks.Open(Filename:=sDateiKundenListe, ReadOnly:=True)
    With wbKundenListe.Worksheets(1)
        Set rng = .Columns(9).Find(What:="Others", LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False, After:=Range("I1"))
        If Not rng Is Nothing Then
            strFirst = rng.Address
            Do
            CustomerName = .Range(.Cells(rng.Row, 1), .Cells(rng.Row, 11))
            CustomerListBox.List = CustomerName
        Set rng = .Columns(9).FindNext(rng)
        Loop While Not rng Is Nothing And strFirst <> rng.Address
        End If
    End With
    wbKundenListe.Close savechanges:=False
    Application.ScreenUpdating = True
    Application.StatusBar = False
End If
If CategoryComboBox.Value = "Test" Then
    
    Application.ScreenUpdating = False
    Application.StatusBar = "Loading Customer Address"
    Set wbKundenListe = Application.Workbooks.Open(Filename:=sDateiKundenListe, ReadOnly:=True)
    With wbKundenListe.Worksheets(1)
        Set rng = .Columns(9).Find(What:="Test", LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False, After:=Range("I1"))
        If Not rng Is Nothing Then
            strFirst = rng.Address
            Do
            CustomerName = .Range(.Cells(rng.Row, 1), .Cells(rng.Row, 11))
            CustomerListBox.List = CustomerName
        Set rng = .Columns(9).FindNext(rng)
        Loop While Not rng Is Nothing And strFirst <> rng.Address
        End If
    End With
    wbKundenListe.Close savechanges:=False
    Application.ScreenUpdating = True
    Application.StatusBar = False
End If
End Sub

[mikerickson]

Instead of setting the .List property of the ListBox, you should use the .AddItem method

CustomerListBox.AddItem CustomerName

[Aranell]

first thanks for your help.
I tried that before but I get than Run-Time Error '-247352571 (80020005)': Type mismatch

[mikerickson]

My misread

With rng.EntireRow
    CustomerListBox.AddItem .Cells(1,1).value
    For I = 1 to 10
        CustiomerListBox.List(CustomerListBox.ListCount - 1, I) = .Cells(1, I+1)
    Next I
end with

[Aranell]

I don't know what you tried to do, but it didn't work.
Maybe you misunderstood me. My Problem is not that the entire row doesn't show on the ListBox, but my problem is that the entire Name Column (Column B) doesn't show in my listbox.
My problem is that only the last name is listed in my listbox, instead of all names with the matched category

[snb]

Please post a sample workbook.

[Aranell]

here is my workbook

[Aranell]

and this is the Excel file for my ListBox... you have to change the url in the code to work curently it's set on: "D:\Documents\ndalj25\Desktop\CustomerDB.xlsx"

[snb]

You can reduce the Initialize event to:

Private Sub UserForm_Initialize()
 CategoryComboBox.List = Split("All|Corporate|Student|Others|Test", "|")
 CustomerName = GetObject("D:\Documents\ndalj25\Desktop\CustomerDB.xlsx").Sheets(1).Cells(1).CurrentRegion.Offset(1)
 CustomerListBox.List = CustomerName
End Sub

and use this to change the content of the CustomerListbox:

Private Sub CategoryComboBox_Change()
    Select Case CategoryComboBox.Value
    Case "All"
        CustomerListBox.List = CustomerName
    Case Else
        For j = 1 To UBound(CustomerName)
            If CustomerName(j, 9) = CategoryComboBox.Value Then c00 = c00 & "_" & j
        Next
        CustomerListBox.List = Application.Index(CustomerName, Application.Transpose(Split(Mid(c00, 2), "_")), 0)
    End Select
End Sub

I fear that your knowledge of VBA doesn't match the task you are trying to accomplish.
Start at the basics/beginning first.

[Aranell]

thanks for the tip... but my issue still remains

[snb]

Not anymore.....

[Aranell]

ehmm .. you didin't defined i or j
so I declared them as integer.
I presume that jj is a typing error
but what about c00 what variable is that??

many thanks in advance

[snb]

I corrected the typing error.
You must be able to deduce from the code what kind of variable is c00.
Check what happens after commenting out 'option Explicit'.