Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

populate data in listbox based on multiple comboboxs don't work

0

hello 

I need  help  to  find  out  and  fixing   the  problem ,  I have  this  code  in  combobox4    doesn't work  despite   the  same  code  works   with  combobox 2,3    The  difference  is  combobox  name  and   column  search    

Private Sub ComboBox4_Change()
Dim r As Long
    If ComboBox4.ListIndex <> -1 Then
        ListBox1.Clear
        With Worksheets("sheet1").Range("A1:A10000")
            For r = 1 To .Rows.Count
                If ComboBox4.Value = .Cells(r, 2).Value Then
                    ListBox1.AddItem .Cells(r, 1).Value
                    ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(r, 2).Value
                    ListBox1.List(ListBox1.ListCount - 1, 2) = .Cells(r, 3).Value
                    ListBox1.List(ListBox1.ListCount - 1, 3) = .Cells(r, 4).Value
                    ListBox1.List(ListBox1.ListCount - 1, 4) = .Cells(r, 5).Value
                    ListBox1.List(ListBox1.ListCount - 1, 5) = .Cells(r, 6).Value

                End If
            Next r
        End With
With ListBox1
.ColumnCount = 6
.ColumnWidths = "150;70;150;90;80;200;0"
End With
    End If
End Sub

Thanks

Answer
Discuss

Answers

0
Selected Answer

Leopard

The problem is that the ComboBox4 values (from K1:K2) are in UPPERCASE but your invoice values in B2:B9 are lowercase (so your test If ComboBox4.Value = .Cells(r, 2).Value Then... always fails)

Make them the same and ListBox 1 will populate properly (and the UF's CommandButton completes the value/ count fields).

Personally I would make them both Titlecase (so it looks better in the UF) in which case, CB4=Unpaid gives TB1and TB2 showing 6 invoices totalling 1390.

Incidentally, your question title refers to "populate... listbox based on multiple comboboxs". Currently it's populated by one of those CBs (e.g. CB4= Unpaid) but the others (CB2 and CB3 retain selections) don't affect the population of ListBox1 (or TB1. TB2). I leave you to sort out any logic you need for the CBs.

Discuss

Discussion

Thanks John    i  have  a problem  with  combobox2    if   i  choose  from combobox4   unpaid   and  choose  combobox2  john    it  brings all names  are  unpaid  it  should   bring   only  john  any  help  , please ?
leopard (rep: 88) Nov 22, '20 at 9:25 am
Leopard

You've done it again! I answered your first question (how to fix ComboBox 4) then you add something else (then something else later probably).

I said at the end of my answer "Currently it's populated by one of those CBs (e.g. CB4= Unpaid) but the others (CB2 and CB3 retain selections) don't affect the population of ListBox1 (or TB1. TB2). I leave you to sort out any logic you need for the CBs."

Clearly your test (before populating the ListBox contents) must AND together all the CB selections. Personally I'd have a separate sub that does that but which is called by each ComboBox change. I'd also that the option for <no selection> ("") added to each ComboBox (and ignored in the test) so that you can select all Upaid (only) or Unpaid by John etc.

Over to you (please...)
John_Ru (rep: 6092) Nov 22, '20 at 10:11 am
thanks   and  sorry  about   ask  aanother   question i  thought   this  part  simple   what  i'm  asking     
leopard (rep: 88) Nov 22, '20 at 10:24 am
Leopard
Hopefully my suggestion of a single macro (which test CB2, CB3 and CB4 against the corresponding columns in your data range) won't be too hard, given your knowledge of VBA.
I'm glad I could help with CB4 (thanks for selecting my answer) and good luck with the AND logic needed to populate your listbox correctly. 
John_Ru (rep: 6092) Nov 22, '20 at 10:53 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login