Userform listbox search

[Urtek1]

So I have a user form that has a combo box that filters the data with the codes
Private Sub Combobox1_change()
Me.ListBox1.Clear
For i = 2 To Application.WorksheetFunction.CountA(Sheet3.Range("D:D"))
If Sheet3.Cells(i, "D").Value = Me.ComboBox1.Value Then
Me.ListBox1.AddItem Sheet3.Cells(i, "A").Value
End If
Next i

End Sub


Private Sub userform_initialize()
Me.ComboBox1.List = Array("Vermeer 755", "2000-320", "2350-1320", "2300-323", "2300-423", "2300-823", "2300-1123", "2300-2023", "2500-1825", "2500-1325", "2500-925", "2450-2423")

End Sub
This code does the combobox list and makes a listbox list based on selection.



Now this code is on a textbox used to search the list for specific items

Sub Show_Product()

Me.ListBox1.Clear

For i = 2 To Application.WorksheetFunction.CountA(Sheet3.Range("A:A"))
If Me.TextBox1.Value = "" Then
Me.ListBox1.AddItem Sheet3.Range("A" & i).Value


Else
If VBA.InStr(UCase(Sheet3.Range("A" & i).Value), UCase(Me.TextBox1.Value)) > 0 Then
Me.ListBox1.AddItem Sheet3.Range("A" & i).Value
End If
End If
Next i

End Sub
Problem I'm having is that it brings up an unfiltered list meaning it brings up products of same name but for different equipment. Basically not searching just the list by the combobox but searching the whole sheet in general. How can I search just what was filtered from combobox.

 

[DanteAmor]

Hi and welcome to MrExcel.

Try the following code.
It will filter in the listbox the matches of the combobox and the textbox.
A button is not necessary. Just select a item from the combo and start typing in the textbox.

Put all the code in the userform:

Option Explicit

Dim a As Variant

Private Sub Combobox1_change()
  Call FilterData
End Sub
Private Sub TextBox1_Change()
  Call FilterData
End Sub

Sub FilterData()
  Dim cmb As String, txt As String
  Dim b As Variant
  Dim i As Long, j As Long
 
  ReDim b(1 To UBound(a), 1 To 2)
  ListBox1.Clear
  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cmb = a(i, 4) Else cmb = ComboBox1.Value
    If TextBox1.Value = "" Then txt = a(i, 1) Else txt = TextBox1.Value
    If a(i, 4) = ComboBox1.Value And LCase(a(i, 1)) Like LCase(txt) & "*" Then
      j = j + 1
      b(j, 1) = a(i, 1)
      b(j, 2) = a(i, 4)
    End If
  Next i
  If j > 0 Then ListBox1.List = b
End Sub

Private Sub userform_initialize()
  ComboBox1.List = Array("Vermeer 755", "2000-320", "2350-1320", "2300-323", "2300-423", _
    "2300-823", "2300-1123", "2300-2023", "2500-1825", "2500-1325", "2500-925", "2450-2423")
  ListBox1.ColumnCount = 2
  a = Sheet3.Range("A2", Sheet3.Range("D" & Rows.Count).End(3)).Value
End Sub

 

[Urtek1]

Hi and welcome to MrExcel.

Try the following code.
It will filter in the listbox the matches of the combobox and the textbox.
A button is not necessary. Just select a item from the combo and start typing in the textbox.

Put all the code in the userform:

Option Explicit

Dim a As Variant

Private Sub Combobox1_change()
  Call FilterData
End Sub
Private Sub TextBox1_Change()
  Call FilterData
End Sub

Sub FilterData()
  Dim cmb As String, txt As String
  Dim b As Variant
  Dim i As Long, j As Long
 
  ReDim b(1 To UBound(a), 1 To 2)
  ListBox1.Clear
  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cmb = a(i, 4) Else cmb = ComboBox1.Value
    If TextBox1.Value = "" Then txt = a(i, 1) Else txt = TextBox1.Value
    If a(i, 4) = ComboBox1.Value And LCase(a(i, 1)) Like LCase(txt) & "*" Then
      j = j + 1
      b(j, 1) = a(i, 1)
      b(j, 2) = a(i, 4)
    End If
  Next i
  If j > 0 Then ListBox1.List = b
End Sub

Private Sub userform_initialize()
  ComboBox1.List = Array("Vermeer 755", "2000-320", "2350-1320", "2300-323", "2300-423", _
    "2300-823", "2300-1123", "2300-2023", "2500-1825", "2500-1325", "2500-925", "2450-2423")
  ListBox1.ColumnCount = 2
  a = Sheet3.Range("A2", Sheet3.Range("D" & Rows.Count).End(3)).Value
End Sub

Thank you so much. Worked perfectly.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.