VBA AutoFilter Using Wildcard not Working - Excel 2010

[jrhunior]

Hello

I've searched all over to and tried everything to get my code to filter a range of cells based on an input into a text box. I want the range to filter on every keystroke in the box and also want there to be a wildcard before and after the text box text. I can get the code to work fine if i remove the "*" before and after the text box data in the Criteria, but i cannot get the wildcard to function. Any help would be appreciated.

Private Sub WidthFilterTextBox_Change()

If WidthFilterTextBox.Value <> "" Then
Sheet2.AutoFilterMode = False
Sheet2.Range("B2:F112").AutoFilter Field:=5, Criteria1:="*" & WidthFilterTextBox.Text & "*"
Else
Sheet2.AutoFilterMode = False
End If
End Sub

 

[Sthrncali]

Use this syntax:

Criteria1:="=*" & WidthFilterTextBox.Text & "*"

 

[jrhunior]

That did not work... As soon as i type something into the text box every row filters out. I've also tried this just using the wildcard at the end so the filter would have to "begin with" the text box and not just "contain" the text box text.

Any other suggestions?

 

[jrhunior]

Do I need to use advanced filter? Or is there a problem with the way the code is called rather than being a workbook macro.

I didn't think there was an issue with the code being called from a textbox change because it works fine if i don't use wildcards in the filter criteria. I just have to type in an exact match... which i don't want to have to do.

Any more help would be appreciated. I'm new to VBA.

 

[Sthrncali]

This works correctly for me. I have a textbox located on a userform. when I start typing, my table filters as expected.

Private Sub TextBox1_Change()
If TextBox1.Value <> "" Then
Sheet1.AutoFilterMode = False
Sheet1.Range("A1:D10").AutoFilter Field:=1, Criteria1:="*" & TextBox1.Text & "*"
Else
Sheet1.AutoFilterMode = False
End If
End Sub

 

[jrhunior]

I don't have the text box on a user form. I just have it on the document by itself.

Does it need to be on a form? Below is my code. It appears the same as yours. I don't know what it could be. Thanks for your help.

Private Sub WidthFilterTextBox_Change()
    If WidthFilterTextBox.Value <> "" Then
        Sheet2.AutoFilterMode = False
        Sheet2.Range("B2:F112").AutoFilter Field:=5, Criteria1:="*" & WidthFilterTextBox.Text & "*"
    Else
        Sheet2.AutoFilterMode = False
    End If
End Sub

 

[jrhunior]

I created a new workbook, put in 1 text box, copied the code, and tried to autofilter a small table. I don't get the results as expected.

The "autofilter" is just putting the filter icons on the first row of cells with data in it and hiding the rest using the "*" & textbox1.text & "*" for the criteria.

I think i may try to use something else or advanced filter. Thanks for the input.

 

[jrhunior]

Ok. I did get this working with AutoFilter. I also added a second text box. So i have two text boxes working together dynamically filtering in place a range of cells.

The issue before was the data in the filter array was stored as numbers and the filter was based on string.

The code is below. If one box is cleared, the autofilter remains on with the criteria from the other text box. Only if both boxes are empty does the autofilter turn off. Works as i need it. This bit of code is much nicer to using the autofilter normally.

Private Sub HeightFilterTextBox_Change()

    If HeightFilterTextBox.Value <> "" Then
        Sheet2.Range("B2:F112").AutoFilter Field:=4, Criteria1:="=*" & HeightFilterTextBox.Text & "*"
    Else
        If WidthFilterTextBox.Value = "" Then
            Sheet2.AutoFilterMode = False
        Else
            Sheet2.Range("B2:F112").AutoFilter Field:=4, Criteria1:="*"
        End If
    End If
End Sub

Private Sub WidthFilterTextBox_Change()

    If WidthFilterTextBox.Value <> "" Then
        Sheet2.Range("B2:F112").AutoFilter Field:=5, Criteria1:="=*" & WidthFilterTextBox.Text & "*"
    Else
        If HeightFilterTextBox.Value = "" Then
            Shet2.AutoFilterMode = False
        Else
            Sheet2.Range("B2:F112").AutoFilter Field:=5, Criteria1:="*"
        End If
    End If
End Sub