I have a user form where they should pick a supplier from a table in the worksheet and when they press the "ShowProducts" command, the form should show all the articles from this supplier in the textbox below.
I made the following code but it keeps giving me an error on the line If Suppl.Value = Me.LstB_Supplier.Value Then.
I have searched and tried different options I have found on this and other sites, but I can't seem to find what it wrong.
Can anyone help me out? Thanks!
Private Sub Cmd_ShowProducts_Click()
Dim Suppl As Range
Dim i As Integer
For Each Suppl In Range("T_Prod_Fix[Supplier Name]")
If Suppl.Value = Me.LstB_Supplier.Value Then
With Me.LstB_Products
.AddItem
.List(i, 0) = Suppl.Offset(0, 1).Value 'article nbr
.List(i, 1) = Suppl.Offset(0, -1).Value 'article name
i = i + 1
End With
End If
Next Suppl
End Sub
If you need to check, whether a value, selected in a list box is present in another list, you need a nested loop. With the first loop you get the selected value and with the inner loop you need to check whether it exists in your range.
E.g. in your case:
For lItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem) Then 'Check for selection
For Each suppl In Range("T_Prod_Fix[Supplier Name]")
If suppl = ListBox1(lItem) Then
'your logic
End If
Next suppl
End If
Next lItem
Related: VBA to get values from a listbox on a spreadsheet in Excel
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments