textbox variable

[Rick Sanderson]

Private Sub btnSearchName_Click()
'Find text from txtChaseString in col 1
'then return values from list
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Me.txtChaseString.Value
'Selection.AutoFilter

Hi, i want the above to autofilter using the data in my form textbox to
autofilter in col A.

Can someone point out the error(s)

TIA
Rick

 

[Dave Peterson]

First, you don't usually have to select something to work with it. And since
you're applying the autofilter, maybe just making sure it's gone and reapplying
it would work ok.

And I don't like to guess what to autofilter, but if that's ok with you, you
could have excel guess based on the current region (try selecting A1 and hit
ctrl-* to see if that selects your range--if it doesn't then don't use this!).

Option Explicit
Private Sub CommandButton1_Click()
'Find text from txtChaseString in col 1
'then return values from list
With ActiveSheet
.AutoFilterMode = False
.Range("a1").CurrentRegion.AutoFilter Field:=1, _
Criteria1:=Me.TextBox1.Value
End With
End Sub

(Watch out, I didn't rename the buttons and textboxes from the defaults.)

 

[Rick Sanderson]

Thanks Dave,
I have managed to autofilter as per your help using the data from one
textbox.
===
Private Sub btnSearch_Click()
With ActiveSheet
.AutoFilterMode = False
Worksheets("sheet1").Range("a1").Select
Selection.AutoFilter Field:=4, Criteria1:=Me.txtChaseName.Value
End With
End Sub
===
Now the only confusion i have is that when filtering if the textbox is left
empy, no results are shown, i assume it is searching for an empty entry, how
can i get around this as it will cause a problem as i try to filter on the
entries in several boxes.

ie.

Private Sub btnSearch_Click()
With ActiveSheet
.AutoFilterMode = False
Worksheets("sheet1").Range("a1").Select
Selection.AutoFilter Field:=1, Criteria1:=Me.txtChaseJobNo.Value
Selection.AutoFilter Field:=2, Criteria1:=Me.txtChaseBranch.Value
Selection.AutoFilter Field:=3, Criteria1:=Me.txtChaseRef.Value
Selection.AutoFilter Field:=4, Criteria1:=Me.txtChaseName.Value
End With
End Sub

 

[Dave Peterson]

It worked ok for me when I left the textbox empty.

If you want to use selects, you can get rid of the "With activesheet":

Option Explicit
Private Sub btnSearch_Click()
Worksheets("sheet1").AutoFilterMode = False
Worksheets("sheet1").Range("a1").Select
Selection.AutoFilter Field:=1, Criteria1:=Me.txtChaseJobNo.Value
Selection.AutoFilter Field:=2, Criteria1:=Me.txtChaseBranch.Value
Selection.AutoFilter Field:=3, Criteria1:=Me.txtChaseRef.Value
Selection.AutoFilter Field:=4, Criteria1:=Me.txtChaseName.Value
End Sub


If you filtered manually, do you see any results. The autofilter is
cumulative--each subsequent filter will reduce the number of visible rows (or
leave the number of visible cells the same depending on your data).

Ohhhh. I think I get it. You want to ignore that textbox if it's left blank
and just filter on the remaining?????

Option Explicit
Private Sub btnSearch_Click()
Worksheets("sheet1").AutoFilterMode = False
Worksheets("sheet1").Range("a:H").Select

If Trim(Me.txtChaseJobNo.Value) <> "" Then
Selection.AutoFilter Field:=1, Criteria1:=Me.txtChaseJobNo.Value
End If

If Trim(Me.txtChaseBranch.Value) <> "" Then
Selection.AutoFilter Field:=2, Criteria1:=Me.txtChaseBranch.Value
End If

If Trim(Me.txtChaseRef.Value) <> "" Then
Selection.AutoFilter Field:=3, Criteria1:=Me.txtChaseRef.Value
End If

If Trim(Me.txtChaseName.Value) <> "" Then
Selection.AutoFilter Field:=4, Criteria1:=Me.txtChaseName.Value
End If

End Sub

I really hate letting excel guess at the range. This time I changed the range
to be filtered to columns A:H. Adjust it as required.

I've always found that excel doesn't guess the range that I want if I have a
blank row or blank column in the middle of my "real" range.

 

[Rick Sanderson]

Thanks Dave (again)<vbg>
Regards
Rick

Dave Peterson wrote:
|| Option Explicit
|| Private Sub btnSearch_Click()
|| Worksheets("sheet1").AutoFilterMode = False
|| Worksheets("sheet1").Range("a:H").Select
||
|| If Trim(Me.txtChaseJobNo.Value) <> "" Then
|| Selection.AutoFilter Field:=1,
|| Criteria1:=Me.txtChaseJobNo.Value End If
||
|| If Trim(Me.txtChaseBranch.Value) <> "" Then
|| Selection.AutoFilter Field:=2,
|| Criteria1:=Me.txtChaseBranch.Value End If
||
|| If Trim(Me.txtChaseRef.Value) <> "" Then
|| Selection.AutoFilter Field:=3,
|| Criteria1:=Me.txtChaseRef.Value End If
||
|| If Trim(Me.txtChaseName.Value) <> "" Then
|| Selection.AutoFilter Field:=4,
|| Criteria1:=Me.txtChaseName.Value End If
||
|| End Sub
||
||
|| Dave Peterson
|| (e-mail address removed)