textbox variable

R

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
 
D

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.)
 
R

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
 
D

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.
 
R

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
|| [email protected]
 
Top