Aranell
08-13-2013, 06:13 AM
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
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