VBA Wildcard

Hi, I am new to VBA and coding.
I have a textbox that if I type in text it looks up cells from 4 columns and if there is a match on the first word then it shows up in a list box.
It works but I want to add wildcard so it will look at the entire cell for words that match
ie: green apple slice in cell A1
I want to type apple or slice and it will get sell A1 displaying green apple slice it my list box. can someone please help?

Private Sub UserForm_Initialize()
TextBox1.SetFocus


End Sub

Private Sub TextBox1_Change()
TextBox1.Text = StrConv(TextBox1.Text, vbProperCase)
TextBox1.SetFocus
Dim i As Long
ListBox1.Clear
On Error Resume Next
For i = 1 To Application.WorksheetFunction.CountA(Sheet11.Range("A:A"))
a = Len(TextBox1.Text)
If UCase(Left(Sheet11.Cells(i, 2).Text, a)) = UCase(Left(TextBox1.Text, a)) Then
ListBox1.AddItem Sheet11.Cells(i, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet11.Cells(i, 2).Value
End If
If UCase(Left(Sheet11.Cells(i, 3).Text, a)) = UCase(Left(TextBox1.Text, a)) Then
ListBox1.AddItem Sheet11.Cells(i, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet11.Cells(i, 2).Value
End If
If UCase(Left(Sheet11.Cells(i, 4).Text, a)) = UCase(Left(TextBox1.Text, a)) Then
ListBox1.AddItem Sheet11.Cells(i, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet11.Cells(i, 2).Value
End If
If UCase(Left(Sheet11.Cells(i, 9).Text, a)) = UCase(Left(TextBox1.Text, a)) Then
ListBox1.AddItem Sheet11.Cells(i, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet11.Cells(i, 2).Value
End If
Next i
End Sub

Open in new window

Carly Carlyle Adams CnfAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Carly

Pretty sure the code can be simplified but this should get you started.
Private Sub TextBox1_Change()
Dim I As Long
Dim strSearch As String

    TextBox1.Text = StrConv(TextBox1.Text, vbProperCase)

    TextBox1.SetFocus
    
    ListBox1.Clear
        
    strSearch = "*" & UCase(TextBox1.Text) & "*"
        
    
    For I = 1 To Application.WorksheetFunction.CountA(Sheet11.Range("A:A"))
    
        If Sheet11.Cells(I, 2).Text Like strSearch Then
            With ListBox1
                .AddItem Sheet11.Cells(I, 1).Value
                .List(.ListCount - 1, 1) = Sheet11.Cells(I, 2).Value
            End With
        End If
        
        If Sheet11.Cells(I, 3).Text Like strSearch Then
            With ListBox1
                .AddItem Sheet11.Cells(I, 1).Value
                .List(.ListCount - 1, 1) = Sheet11.Cells(I, 2).Value
            End With
        End If
        
        If Sheet11.Cells(I, 4).Text Like strSearch Then
            With ListBox1
                .AddItem Sheet11.Cells(I, 1).Value
                .List(.ListCount - 1, 1) = Sheet11.Cells(I, 2).Value
            End With
        End If
        
        If Sheet11.Cells(I, 9).Text Like strSearch Then
            With ListBox1
                .AddItem Sheet11.Cells(I, 1).Value
                .List(.ListCount - 1, 1) = Sheet11.Cells(I, 2).Value
            End With
        End If
        
    Next I
    
End Sub

Open in new window

0
 
Carly Carlyle Adams CnfAuthor Commented:
Wow, that's works great, that is so cool, thank you so much. Norie.
I have been working on this for a week watching videos and trying all kinds of things.
0
 
Carly Carlyle Adams CnfAuthor Commented:
Thank you so much
0
 
Carly Carlyle Adams CnfAuthor Commented:
Update on this to help the next person.
Code below.

  1. Private Sub TextBox1_Change()
  2. Dim I As Long
  3. Dim strSearch As String
  4.    TextBox1.Text = StrConv(TextBox1.Text, vbProperCase)
  5.    TextBox1.SetFocus
  6.    ListBox1.Clear
  7.    strSearch = "*" & UCase(TextBox1.Text) & "*"
  8.    'Note: Why For I = 10 as I have no info in rows 1 to 9 this is a heading. So change 10 to 1 if don't what to skip rows.
  9.    For I = 10 To Application.WorksheetFunction.CountA(Sheet11.Range("A:A"))
  10.    'This code will search columns 2,3,4 & 9.
  11.        If UCase(Sheet11.Cells(I, 2).Text) & UCase(Sheet11.Cells(I, 3).Text) & UCase(Sheet11.Cells(I, 4).Text) & UCase(Sheet11.Cells(I, 9).Text) Like strSearch Then
  12.            With ListBox1
  13.                .AddItem Sheet11.Cells(I, 1).Value
  14.                .List(.ListCount - 1, 1) = Sheet11.Cells(I, 2).Value
  15.            End With
  16.        End If
  17.    Next I
  18. End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.