Excel VBA: Autofilter using textbox values

Question

I tried the following to filter (between filter) my worksheet based on 2 values. The 2 values are input in textbx1 and textbox2 in userform 'A1_Filter_Range'.

But the code doesn't work ... I get error: Run-Time error'424': Object required'

    ActiveSheet.Range("$A$1:$AD$2000").AutoFilter Field:=17, _
    Criteria1:=TextBox1.Value, _
        Operator:=xlAnd, _
    Criteria2:=TextBox2.Value

Any ideas? Thanks SMORF

Your code is having a hard time finding the parent userform for the textboxes. A solution will depend where the above snippet resides. — user4039065
– user4039065, Commented May 19, 2015 at 19:07

Physikbuddha · Accepted Answer · 2015-05-19 19:16:32Z

2

Simple solution, you have to tell Excel where to look for the TextBoxes:

ActiveSheet.Range("$A$1:$AD$2000").AutoFilter Field:=17, _
    Criteria1:=ActiveSheet.TextBox1.Value, _
    Operator:=xlAnd, _
    Criteria2:=ActiveSheet.TextBox2.Value

answered May 19, 2015 at 19:16

Physikbuddha

1,6721 gold badge15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Physikbuddha Over a year ago

If the TextBoxes are placed on another worksheet than the current one, replace ActiveSheet. with Worksheets(#). where # is the worksheet index.

Vega · Accepted Answer · 2017-11-03 09:16:47Z

1

If TextBox1.Text <> "" Then

        TextBox1.BackColor = RGB(254, 254, 22) 'yellow

        Dim word As String


        word = "*" & TextBox1.Text & "*"
        Selection.AutoFilter Field:=1, Criteria1:=word, Operator:=xlAnd

    Else
        Selection.AutoFilter Field:=1
        TextBox1.BackColor = RGB(55, 255, 255) 'cyan

    End If
            'ActiveSheet.Protect Password:=""

End Sub

Private Sub TextBox2_Change()
                'ActiveSheet.Unprotect Password:=""

 If TextBox2.Text <> "" Then

        TextBox2.BackColor = RGB(254, 254, 22) 'yellow

        Dim slovo As String


        word = "*" & TextBox2.Text & "*"
        Selection.AutoFilter Field:=2, Criteria1:=word, Operator:=xlAnd

    Else
        Selection.AutoFilter Field:=2
        TextBox2.BackColor = RGB(55, 255, 255) 'cyan

    End If
                'ActiveSheet.Protect Password:=""
End Sub

edited Nov 3, 2017 at 9:16

Vega

28.8k28 gold badges121 silver badges151 bronze badges

answered Nov 3, 2017 at 8:58

user8879522

111 bronze badge

1 Comment

Thomas Rollet Over a year ago

Can you explain to the author what is wrong with his code?

Collectives™ on Stack Overflow

Excel VBA: Autofilter using textbox values

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related