Groups

    Excel Dynamic Autofilter Help

    Hi,

    I was wondering whether any HUKD Excel geniuses might be able to come to my aid?

    Basically I have got a few textboxes on a spreadsheet, when you enter text into the textbox, it then filters the data as you type.

    However the VBA that I have used, does not link the text boxes. For example:

    In the textbox for Firstname, if I write in 'J', it will then filter all the firstnames with the character 'J' in (James, Harj etc)

    In the textbox for Surname, if I write in 'S', it will filter all the the surnames with the character 'S' in (Smith, Jones etc)

    However, the VBA does not combine the textboxes, if I write something in firstname textbox, and then something in the surname textbox, it ignores what I originally wrote in the firstname textbox.

    Could someone please help me out, so that the filter works with both textboxes?

    I would really appreciate it

    The VBA I have used is:

    Private Sub TextBox1_Change()

    If Len(TextBox1.Value) = 0 Then

    Sheet10.AutoFilterMode = False

    Else

    If Sheet10.AutoFilterMode = True Then

    Sheet10.AutoFilterMode = False

    End If

    Sheet10.Range("A26:E" & Rows.Count).AutoFilter Field:=3, Criteria1:="*" & TextBox1.Value & "*"

    End If

    End Sub

    Private Sub TextBox2_Change()

    If Len(TextBox2.Value) = 0 Then

    Sheet10.AutoFilterMode = False

    Else

    If Sheet10.AutoFilterMode = True Then

    Sheet10.AutoFilterMode = False

    End If

    Sheet10.Range("A26:E" & Rows.Count).AutoFilter Field:=4, Criteria1:="*" & TextBox2.Value & "*"

    End If

    End Sub

    4 Comments

    Had a quick look with minimal testing try the below...

    Private Sub TextBox2_Change()
    If Len(TextBox2.Value) = 0 Then

    Sheet10.AutoFilterMode = False

    Else

    If Sheet10.AutoFilterMode = True Then

    Sheet10.AutoFilterMode = False

    End If

    Call DualFilter

    End If
    End Sub


    Private Sub TextBox1_Change()
    If Len(TextBox1.Value) = 0 Then

    Sheet10.AutoFilterMode = False

    Else

    If Sheet10.AutoFilterMode = True Then

    Sheet10.AutoFilterMode = False

    End If

    Call DualFilter

    End If
    End Sub

    Sub DualFilter()
    Sheet10.Range("A26:E" & Rows.Count).AutoFilter Field:=3, Criteria1:="*" & TextBox1.Value & "*"
    Sheet10.Range("A26:E" & Rows.Count).AutoFilter Field:=4, Criteria1:="*" & TextBox2.Value & "*"
    End Sub

    Original Poster

    Samcoop. Thank you very much for your help. I did find a way very similar to the way taht you have done it. Although your way is a lot more tidier, so I shall use your way.

    Thanks again

    Original Poster

    Thanks again Samcoop, really appreciate it

    no problem happy to help
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. cheap glasses websites11
      2. Cheapest way to call landline, UK to Ireland55
      3. Import charges from USA44
      4. Mayweather vs Mcgregor33

      See more discussions