doesn't show data in listbox on form without any error in this code

[Abdo]

Hello
I try to populate data in listbox on form based on selected items from combobox1,combobox2
combobox1 will match with column A and combobox2 will match with column E then should populate data in list box
here is code getting from this forum and I modified based on my requirements.

Private Sub CommandButton1_Click()
   
   Dim i As Long, j As Long, k As Long
    Dim cb1 As String
    Dim cb2 As String
    
    a = Sheets("Sheet3").Range("A1:F" & Sheets("Sheet3").Range("F" & Rows.Count).End(3).Row).Value
    
     With ListBox2
     .ColumnWidths = "40;50;50;40;60;40"
     .columnCount = 6
     .Font.Size = 10
     
        End With
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  
  For i = 1 To UBound(a)
  If ComboBox1.Value = "" Then cb1 = a(i, 1) Else cb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cb2 = a(i, 5) Else cb1 = ComboBox2.Value
    If LCase(a(i, 1)) Like LCase(cb1) & "*" And LCase(a(i, 5)) Like LCase(cb2) & "*" Then
  k = k + 1
      For j = 1 To 6
        b(k, j) = a(i, j)
      Next
    End If
    Next
      If k > 0 Then ListBox2.List = b

    If ListBox2.ListCount = 0 Then
        MsgBox "doesn't show data"
    End If
   
End Sub

I hope some body help me.
thanks

 

[DanteAmor]

Hi Abdo:

Check this:

- The commandbutton is not necesary.​

- Now every time you modify any combobox the listbox will automatically be filled.​

- Must be cb2:​

If ComboBox2.Value = "" Then cb2 = a(i, 5) Else cb1 cb2 = ComboBox2.Value​

- I assume you already loaded the values into each combobox.​

Replace your code for this:

Dim a As Variant                'At the beginning of all the code

Private Sub ComboBox1_Change()
  Call FilterData
End Sub
Private Sub ComboBox2_Change()
  Call FilterData
End Sub

Private Sub FilterData()
  Dim i As Long, j As Long, k As Long
  Dim cb1 As String, cb2 As String
   
  ListBox2.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cb1 = a(i, 1) Else cb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cb2 = a(i, 5) Else cb2 = ComboBox2.Value
    If LCase(a(i, 1)) Like LCase(cb1) & "*" And LCase(a(i, 5)) Like LCase(cb2) & "*" Then
      k = k + 1
      For j = 1 To 6
        b(k, j) = a(i, j)
      Next
    End If
  Next
 
  If k = 0 Then
    MsgBox "doesn't show data"
  Else
    ListBox2.List = b
  End If
End Sub

Private Sub UserForm_Activate()
  a = Sheets("Sheet3").Range("A1:F" & Sheets("Sheet3").Range("F" & Rows.Count).End(3).Row).Value
  With ListBox2
    .RowSource = ""
    .ColumnWidths = "40;50;50;40;60;40"
    .ColumnCount = 6
    .Font.Size = 10
  End With

  Call FilterData
End Sub

 

[Abdo]

still doesn't show data by show message "doesn't show data" !

 

[DanteAmor]

still doesn't show data by show message "doesn't show data" !

It is not enough to put it does not work, you must put what data you have on the sheet, what you are putting in each combobox.

Check these 4 points:

1. Did you copy the entire code?
2. Remember that the declaration of the variable 'a' must go at the beginning of all the code.
3. Did you make changes to my code?
4. How are you loading the comboboxes and what data does each combobox have?

In my tests the filters work wonderfully. See the example below.

Without filter:

With filter combobox1:

With filter combo1 and combo2:

 

[Abdo]

1. Did you copy the entire code?

surely yes.

1. Remember that the declaration of the variable 'a' must go at the beginning of all the code.

I made sure about it .

1. Did you make changes to my code?

surely I didn't

• How are you loading the comboboxes and what data does each combobox have?

I did by manually link cells with row source in properties form .I have not code to do automatically.
here is data

da.xlsm
	A	B	C	D	E	F
1	CODE	BRAND	PRICE	QTY	WHAREHOUSE	DATE
2	100	Produit	50	149	magasin1	01/05/2024
3	100	Produit	50	17	magasin1	01/01/2024
4	100	Produit	50	25	magasin2	01/01/2024
5	100	Produit	50	25	magasin3	01/01/2024
6	101	Produit1	70	0	magasin	01/01/2024
7	101	Produit1	70	13	magasin1	01/01/2024
8	101	Produit1	70	51	magasin2	01/01/2024
9	102	Produit2	85	84	magasin3	01/01/2024
10	102	Produit2	85	39	magasin	01/01/2024
11	102	Produit2	85	9	magasin1	01/01/2024
12	103	Produit3	90	50	magasin2	01/01/2024
13	103	Produit3	90	83	magasin3	01/01/2024
14	104	Produit4	90	65	magasin3	01/01/2024
15	101	Produit1	70	6	magasin	01/05/2024
16	102	Produit2	85	11	magasin	01/05/2024
17	103	Produit3	90	50	magasin3	01/05/2024
18	100	Produit	50	12	magasin	01/05/2024
Sheet3

here is used code

Dim a As Variant                'At the beginning of all the code

Private Sub ComboBox1_Change()
  Call FilterData
End Sub
Private Sub ComboBox2_Change()
  Call FilterData
End Sub

Private Sub FilterData()
  Dim i As Long, j As Long, k As Long
  Dim cb1 As String, cb2 As String
   
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cb1 = a(i, 1) Else cb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cb2 = a(i, 5) Else cb2 = ComboBox2.Value
    If LCase(a(i, 1)) Like LCase(cb1) & "*" And LCase(a(i, 5)) Like LCase(cb2) & "*" Then
      k = k + 1
      For j = 1 To 6
        b(k, j) = a(i, j)
      Next
    End If
  Next
 
  If k = 0 Then
    MsgBox "doesn't show data"
  Else
    ListBox1.List = b
  End If
End Sub

Private Sub UserForm_Activate()
  a = Sheets("Sheet3").Range("A1:F" & Sheets("Sheet3").Range("F" & Rows.Count).End(3).Row).Value
  With ListBox1
    .RowSource = ""
    .ColumnWidths = "40;50;50;40;60;40"
    .ColumnCount = 6
    .Font.Size = 10
  End With

  Call FilterData
End Sub

 

[DanteAmor]

I did by manually link cells with row source in properties form .I have not code to do automatically.
here is data

The problem is the data you are putting in the combobox.
Delete what you did. I do it with the code.

combobox1 will match with column A and combobox2 will match with column E then should populate data in list box

If I load the combos it works wonderfully:

Use the following code, I added some lines to load the comboboxes.

Dim a As Variant                'At the beginning of all the code

Private Sub ComboBox1_Change()
  Call FilterData
End Sub
Private Sub ComboBox2_Change()
  Call FilterData
End Sub

Private Sub FilterData()
  Dim i As Long, j As Long, k As Long
  Dim cb1 As String, cb2 As String
    
  ListBox2.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  
  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cb1 = a(i, 1) Else cb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cb2 = a(i, 5) Else cb2 = ComboBox2.Value
    If LCase(a(i, 1)) Like LCase(cb1) & "*" And LCase(a(i, 5)) Like LCase(cb2) & "*" Then
      k = k + 1
      For j = 1 To 6
        b(k, j) = a(i, j)
      Next
    End If
  Next
  
  If k = 0 Then
    MsgBox "doesn't show data"
  Else
    ListBox2.List = b
  End If
End Sub

Private Sub UserForm_Activate()
  Dim dic1 As Object, dic2 As Object
  Dim i As Long
  
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  a = Sheets("Sheet3").Range("A1:F" & Sheets("Sheet3").Range("F" & Rows.Count).End(3).Row).Value
  With ListBox2
    .RowSource = ""
    .ColumnWidths = "40;50;50;40;150;40"
    .ColumnCount = 6
    .Font.Size = 10
  End With

  For i = 2 To UBound(a, 1)
    dic1(a(i, 1)) = Empty
    dic2(a(i, 5)) = Empty
  Next
  
  ComboBox1.List = dic1.keys
  ComboBox2.List = dic2.keys

  Call FilterData
End Sub

 

[Abdo]

now works greatly .
many thanks for assistance.