Excel Dynamic Autofilter Help - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HotUKDeals, you accept our cookie and privacy policy.
Get the HotUKDeals app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Excel Dynamic Autofilter Help

£0.00 @
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 … Read More
allsa001 Avatar
6m, 3w agoPosted 6 months, 3 weeks ago
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
allsa001 Avatar
6m, 3w agoPosted 6 months, 3 weeks ago
Options
Best Answer
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

All Responses

(4) Jump to unreadPost an answer
Responses/page:
#1
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
#2
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
#3
Thanks again Samcoop, really appreciate it
#4
no problem happy to help :)

Post an Answer

You don't need an account to leave a response. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!