Autofilter columns according to single or multiple criteria

[Samadhan Gaikwad]

Hi All,
I have below code (from Youtube: Dynamic filters in Excel (Filter as you type) - Excel VBA Tutorial) for filtering columns according to text from header/textbox as one type. But getting runtime error 1004.
I want:
1. To fix the existing error.
2. To apply the filters using criteria in one column or more than one (i.e. when user gives input in more than one textbox).
3. To know is this method OK as user types vba starts running, filter gets applied or another approach as soon as user hit button after adding text in textbox, filter will be applied.

Private Sub TextBox1_Change()
If Len(TextBox1.Value) = 0 Then
    Sheet1.AutoFilterMode = False
Else
    If Sheet1.AutoFilterMode = True Then
        Sheet1.AutoFilterMode = False
    End If
'Sheet1.Range("A2:B" & Rows.Count).AutoFilter field:=1, Criteria1:="*" & TextBox1.Value & "*"
Sheet1.Range("A2:B100").AutoFilter field:=1, Criteria1:="*" & TextBox1.Value & "*"
End If
End Sub

Private Sub TextBox2_Change()
If Len(TextBox2.Value) = 0 Then
    Sheet1.AutoFilterMode = False
Else
    If Sheet1.AutoFilterMode = True Then
        Sheet1.AutoFilterMode = False
    End If
'Sheet1.Range("A2:B" & Rows.Count).AutoFilter field:=2, Criteria1:="*" & TextBox2.Value & "*"
Sheet1.Range("A2:B100").AutoFilter field:=2, Criteria1:="*" & TextBox2.Value & "*"
End If
End Sub

 

[Belleke]

Something like this perhaps?

 

[Samadhan Gaikwad]

Something like this perhaps?

Thank you, Belleke. I have trouble running this on excel 2016. I will test tomorrow in 2013. Right now, am unable to add text in text box.

 

[Samadhan Gaikwad]

I have got excel macro code from this and works fine too from youtube only -
Dynamic Searching through VBA for MS Excel (without Filter function) -

Just would need to look into it when there be huge database that might cause perf. issue, if any.

 

[Belleke]

Hello,

I have trouble running this on excel 2016

The example that I send you was made with 2016. ????

 

[Samadhan Gaikwad]

Hello,

The example that I send you was made with 2016. ????

Hmm. Just moved textbox from its place, and its started working.
But to Clear button not working with 1st click, wokrs on 2nd or 3rd.

 

[Samadhan Gaikwad]

I have just updated above sheet:
1. To clear all text boxes with button.
2. Change and restore textbox backcolor when text box content is added/removed.

I just want to resize content in the textbox when user changes column width (because I have set text box properties to "Move and Size with cells"). I.e. I want to change original text box width with changed column width.

 

[Samadhan Gaikwad]

Any help or suggestion how I can update textbox content properties' to its original size when textbox width is changed (as a result of changing column width). You can see current behavior in above image.