Create a working search box

[luckysmit]

I have tried a few ways to use a sech box in a user form, each time i find a road block. i am only getting back in to using vba and this has stretched my knowledge,

in the example i am tiring to allow the user to type in a search term and have it retune the top results, in this case i want to use
text box 7,
to look at sheet 3
and retune all the results to list box 1

Code is below image

Thanks for all the help so far. I have been going through your forum to get this far. its a great resource. Now i am stuck i really need individual hep. Thanks again for any time you can give.

Private Sub Clear()
Dim ctl As MSForms.Control
For Each ctl In Me.Controls
    Select Case TypeName(ctl)
        Case "TextBox"
        ctl.Text = ""
        Case "CheckBox", "OptionButton", "ToggleButtion"
        ctl.Value = False
        Case "ComboBox", "ListBox"
        ctl.ListIndex = -1
    End Select
Next ctl
End Sub

Private Sub CommandButton2_Click()
Call Clear
End Sub

Private Sub TextBox7_Change()
  Dim ws As Worksheet
  Dim i As Long, j As Long, k As Long
  Dim cad As String
Set ws = Sheet3
  With Me.ListBox1
   .Clear
   .ColumnCount = 7
   .ColumnWidths = "80 pt;180 pt;80 pt;80 pt;80 pt;80 pt;80 pt"
   .ColumnHeads = 0
   For i = 1 To ws.Range("B" & Rows.Count).End(3).Row
        cad = ""
        For j = 1 To Columns("O").Column
   Next
   If LCase(cad) Like "*" & LCase(TextBox1.Text) & "*" Then
      .AddItem
      .List(k, 0) = ws.Cells(i, 1)
      .List(k, 1) = ws.Cells(i, 3)
      .List(k, 2) = ws.Cells(i, 4)
      .List(k, 3) = ws.Cells(i, 16)
      .List(k, 4) = ws.Cells(i, 17)
      .List(k, 5) = ws.Cells(i, 18)
      .List(k, 6) = ws.Cells(i, 10)
       k = k + 1
   End If
    Next
    If .ListCount = 0 Then
    MsgBox "No match"
   TextBox1.SetFocus
    End If
  End With
End Sub

Private Sub CommandButton1_Click()
Dim emptyRow As Long
Dim screwID As Long
'Make Sheet1 active
 Sheet5.Activate
'Determine emptyRow
 emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = DateTime.Now
 Cells(emptyRow, 2).Value = TextBox1.Value
 Cells(emptyRow, 3).Value = TextBox5.Value
 Cells(emptyRow, 4).Value = TextBox6.Value
 Cells(emptyRow, 5).Value = TextBox4.Value
 Cells(emptyRow, 6).Value = TextBox2.Value
 Cells(emptyRow, 7).Value = TextBox3.Value
Dim Output As Integer
If MsgBox("Part(s) Booked Out by " & TextBox1.Value & vbCrLf & vbCrLf & "Book out another part?", _
vbYesNo, "Booking Status") = vbYes Then
   TextBox2.Value = ""
   TextBox3.Value = ""
   Else
   Sheet1.Activate
   Call Clear
   Call CloseForm
End If
End Sub

Private Sub CloseForm()
Unload Me
ActiveWorkbook.Save
End Sub