PDA

View Full Version : changing Listbox based on an other combobox



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

mikerickson
08-13-2013, 06:48 AM
Instead of setting the .List property of the ListBox, you should use the .AddItem method

CustomerListBox.AddItem CustomerName

Aranell
08-13-2013, 06:57 AM
first thanks for your help.
I tried that before but I get than Run-Time Error '-247352571 (80020005)': Type mismatch

mikerickson
08-13-2013, 12:03 PM
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
08-14-2013, 12:14 AM
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
08-14-2013, 12:34 AM
Please post a sample workbook.

Aranell
08-14-2013, 01:13 AM
here is my workbook

Aranell
08-14-2013, 01:16 AM
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
08-14-2013, 01:35 AM
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
08-14-2013, 01:54 AM
thanks for the tip... but my issue still remains

snb
08-14-2013, 01:56 AM
Not anymore.....

Aranell
08-14-2013, 02:09 AM
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
08-14-2013, 02:54 AM
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'.