PDA

View Full Version : Conditional autofilter based on form



enfantter
08-28-2007, 02:00 AM
Hey,

Im creating a tool for getting an overview over some posts.
All post have a start date and an end date.
I know how to do a normal autofilter in excel, but this is not enough. I need to be able to specify the date interval which i wish to be shown.

I have tried to program this part in a form

--

Private Sub cmd2_Click()
Dim txt1 As Date

txt1 = val(TextBox1.Text)
range("K8:K335").Select
range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">07-08-2007", Operator:=xlAnd
ActiveWindow.SmallScroll Down:=-295
End Sub

--
where K8:K335 is the column with start dates.
The problem is though, when i make the filter through vba, where i specify a date in a textbox it filters out all records. Im thinking that i need to specify something about dates??

Please reply for more detail.:dunno

/jack

Bob Phillips
08-28-2007, 03:12 AM
Not tested, but try this



Private Sub cmd2_Click()
Dim txt1 As Date
Dim rng As Range

txt1 = Val(TextBox1.Text)
Set rng = Range("K8:K335")
Set rng = Range(rng, rng.End(xlDown))
rng.AutoFilter
rng.AutoFilter Field:=1, Criteria1:=Format(DateSerial(2007, 8, 7), Range("K9").NumberFormat), Operator:=xlAnd
End Sub

enfantter
08-28-2007, 03:35 AM
tnx xld,

but it still seems to filter out everything.
Unfurtunately i forgot to post one thing.

the code should be more like the following, since i want to specify my date in the form. The code is inside the form.

--

Private Sub cmd2_Click()
Dim txt1 As Date
Dim rng As range

txt1 = val(TextBox1.Text)
Set rng = range("K8:K335")
Set rng = range(rng, rng.End(xlDown))
rng.AutoFilter
rng.AutoFilter Field:=1, Criteria1:=Format(txt1, range("K9").NumberFormat), Operator:=xlAnd
End Sub

Bob Phillips
08-28-2007, 04:18 AM
This works for me



Dim txt1 As Date
Dim rng As Range

txt1 = CDate(TextBox1.Text)
Set rng = Range("K8:K335")
Set rng = Range(rng, rng.End(xlDown))
rng.AutoFilter
rng.AutoFilter Field:=1, Criteria1:=Format(txt1, Range("K9").NumberFormat), Operator:=xlAnd

enfantter
08-28-2007, 06:14 AM
Super, ill check that when possible ..
In what format do you enter your date in textbox1??

Bob Phillips
08-28-2007, 06:15 AM
Any format, as long as it is a date, because I cast it to a date type, and then format to comply with the data.

enfantter
08-28-2007, 06:25 AM
ok, I see - nice, i think my real truble was the cdate thing ...
Appriciate the help - experienced user?

Bob Phillips
08-28-2007, 06:35 AM
That was part of it, but the formatting is also key.

enfantter
08-28-2007, 11:01 PM
Ok, it still filters out all the records - does anybody have a clue to why?!
Maybe its something obvious that i forgot, please let me know if you have any clue...
Could it be that you dont format the critieria as a date??
--
Criteria1:=Format(txt1, Range("K9").NumberFormat)

enfantter
08-28-2007, 11:33 PM
Should I post the ark ??

enfantter
08-28-2007, 11:33 PM
ark=book:hi:

enfantter
08-28-2007, 11:37 PM
excel

enfantter
08-28-2007, 11:46 PM
finally here it is

Bob Phillips
08-29-2007, 12:42 AM
Works fine for me, I put in a start and end date 03 3/1/2007 and got two records.

What is Opgaveart?

enfantter
08-29-2007, 12:47 AM
Ok, strange ... Can you give me the exact values you enter... its strange since end date is not activated (no code assigned) - did you do that ?!
Opgaveart was thought to be a third parameter which could be filtered upon (from a dropdownlist). Its characteristics.

Bob Phillips
08-29-2007, 12:55 AM
No I didn't do it, I just assumed it was working.

I put in a date like 3/1/2007 and it worked.

Just tried 3/9/2007, loads returned.

Then tried 03/01/2007, two returned.

Tried 03-Sep-2007 still worked.

Tried 2007-01-03, two returned.

As I said the date format doesn't matter as long as you use valid date formats. It does fail with an invalid date, so an improvement would be



Private Sub cmd2_Click()

Dim txt1 As Date
Dim rng As range

Set rng = range("K8:K335")
rng.AutoFilter
On Error Resume Next
txt1 = CDate(TextBox1.Text)
On Error GoTo 0
If txt1 <> 0 Then
Set rng = range(rng, rng.End(xlDown))
rng.AutoFilter Field:=1, Criteria1:=Format(txt1, range("K9").NumberFormat), Operator:=xlAnd
End If
End Sub

enfantter
08-29-2007, 01:05 AM
No - still filters out everything for me ..
Wonders if im doing something obvious wrong ...:bug:

Bob Phillips
08-29-2007, 01:25 AM
All date styles?

What are you entering into the start date?

If you 3/1/2007 in a cell, say A1, and then add =TEXT(A1,"mmm") in B1, what does B1 return?

enfantter
08-29-2007, 01:37 AM
jan ...

Bob Phillips
08-29-2007, 01:43 AM
As I asked, what style did you use when inputting vthe date in the form?

What style do you input dates in a spreadsheet normally?

enfantter
08-29-2007, 01:54 AM
Ok i put it like 03-01-2007

enfantter
08-29-2007, 03:34 AM
I also need to put above sign, in order to get the records that have higher values than the criteria that i put.

rng.AutoFilter Field:=1, Criteria1:=(<?!)Format(txt1, range("K9").NumberFormat)", Operator:=xlAnd

Bob Phillips
08-29-2007, 04:27 AM
rng.AutoFilter Field:=1, Criteria1:="<" & Format(txt1, range("K9").NumberFormat), Operator:=xlAnd

enfantter
08-29-2007, 06:53 AM
Ok, i works fine now !!:)
Is there a way to include secondary criterion (end date)??
Maybe something like this:
Private Sub cmd2_Click()

Dim txt1 As Date
Dim rng As range

Set rng = range("a8:l335")
rng.AutoFilter
On Error Resume Next
txt1 = CDate(TextBox1.Text)
txt2 = CDate(TextBox2.Text)
On Error GoTo 0
If txt1 <> 0 Then
Set rng = range(rng, rng.End(xlDown))
rng.AutoFilter Field:=1, Criteria1:=">=" & Format(txt1, range("K9").NumberFormat), Operator:=xlAnd
End If
If txt2 <> 0 Then
Set rng = range(rng, rng.End(xlDown))
rng.AutoFilter Field:=1, Criteria1:="<=" & Format(txt2, range("L9").NumberFormat), Operator:=xlAnd
End If
End Sub

Bob Phillips
08-29-2007, 07:06 AM
Like this?



Private Sub cmd2_Click()

Dim txt1 As Date
Dim txt2 As Date
Dim rng As range

Set rng = range("K8:L335")
rng.AutoFilter
On Error Resume Next
txt1 = CDate(TextBox1.Text)
txt2 = CDate(txtbox2.Text)
On Error GoTo 0
If txt1 <> 0 Then
Set rng = range(rng, rng.End(xlDown))
rng.AutoFilter Field:=1, _
Criteria1:=">=" & Format(txt1, range("K9").NumberFormat)
End If
If txt2 <> 0 Then
rng.AutoFilter Field:=2, _
Criteria1:="<=" & Format(txt1, range("L9").NumberFormat)
End If
End Sub

enfantter
08-29-2007, 08:28 AM
Exactly what I needed!! Great!
Do you by any chance know if i can make a listbox for the last criterion, so that the user can only enter specified values??

Bob Phillips
08-29-2007, 10:02 AM
Yeah, easy enough. Where will the dates come from?

enfantter
09-08-2007, 06:37 AM
Ok its like this now ..
I want to be able to filter on the row called ansvarlig, by picking from the listbox as well but i doesnt seem to work.