Search for word in any position of a term with multiple words

Question

On my VBA UserForm, I have a TextBox (Txt1). I use it as a search bar for my ListBox (Lst1).

When I write any expression to Txt1, I can find what I am looking for in Lst1. These two (Txt1 and Lst1) are integrated into my stock program.

For finding a stock name, I need to write the exact stock name to Txt1.

For example;

If I am looking for "Siemens Overcurrent Relay"
I need to write "Siemens" then "Overcurrent" and then "Relay"

I want when I write "Relay" to Txt1 to see a list of the all stock names that include "Relay", whether it's in the middle or at the end of the stock name.

With my code below, I just see the list of the first word in Lst1.
In my case that means, I can just list "Siemens"

How to get the list to display every stock name that contains the search term?

Private Sub ListBox1_Click()

Dim i As Long, lastrow As Long
lastrow = Sheets("TümListe").Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
    If CStr(Sheets("TümListe").Cells(i, "D").Value) = (Me.ListBox1.Value) Then
        Me.TextBox2 = Sheets("TümListe").Cells(i, "E").Value
        Me.TextBox3 = Sheets("TümListe").Cells(i, "F").Value
        Me.TextBox4 = Sheets("TümListe").Cells(i, "B").Value
        Me.TextBox5 = Sheets("TümListe").Cells(i, "C").Value
    End If
Next
End Sub

Private Sub TextBox1_Change()

Dim i As Long
Me.TextBox1.Text = StrConv(Me.TextBox1.Text, 1)
Me.ListBox1.Clear
For i = 2 To Application.WorksheetFunction.CountA(Sayfa281.Range("D:D"))
    a = Len(Me.TextBox1.Text)
    If Left(Sayfa281.Cells(i, 4).Value, a) = Left(Me.TextBox1.Text, a) Then
        Me.ListBox1.AddItem Sayfa281.Cells(i, 4).Value
        Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Sayfa2.Cells(i, 6).Value
    End If
Next i
End Sub

Answer 1

Instead of this line

If Left(Sayfa281.Cells(i, 4).Value, a) = Left(Me.TextBox1.Text, a) Then

Use Instr function so that it will search the word up. Like this:

If InStr(Sayfa281.Cells(i, 4).Value, Me.TextBox1.Text) >= 1 Then

Thanks to SeanC for the Instr function VBA equivalent to Excel's "Search()"