Solved: Setting filters from a form

[boc_est1986]

I have an input form which asks people to put in a date range (date from and date to) i then want to apply a filter on the column between the inputted date range. When the filter is applied the cells all go blank as if nothing meets the criteria. If i manual click on the filter then go into custom the correct dates are in there and if i click ok the data shows up. Can anyone help?

Here is my code
[vba]Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = Excel.Application
Set xlBook = xlApp.Workbooks.Open("X:\Procurement\VSM Machining\First Article Inspection\First Article Database Machining.xls")
Set xlSheet = xlBook.Sheets("FIRST ARTICLE INSPECTION")

xlSheet.Activate
Range("AB1") = UserForm1.ComboBox1.Text
Range("AC1") = UserForm1.TextBox1.Text
Range("AD1") = UserForm1.TextBox2.Text

Range("B2:Z2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=Range("AB1")
Selection.AutoFilter Field:=13, Criteria1:=">=" & Range("AC1"), Operator:= _
xlAnd, Criteria2:="<=" & Range("AD1")
Range("B2").Select[/vba]

[Kenneth Hobs]

Use CDate() to convert text dates into numerical dates. Obviously, all textbox values will be a text string. There is a danger in working with text dates in that not everyone enters the same format that your computer has set. I suggest adding a label to show them the correct method. Always work with numerical dates in your workbook and format as needed.

e.g.
[vba]Range("AB1").Value = CDate(UserForm1.ComboBox1.Text )
Range("AB1").NumberFormat = "mm/dd/yyyy"[/vba]

[lynnnow]

Please explain what is in your combobox? That could be a possible trigger.

Also, instead of storing the values to the workbook, try setting up the autofilter directly. Is that a no-no for you?

[boc_est1986]

thanks for your replies

when trying to use
[VBA]Range("AB1").Value = CDate(UserForm1.ComboBox1.Text )[/VBA]
i get a runtime error 13: type mismatch.

The combobox containes names, this part of the filter is working fine. If i disable the date filters the names work fine, if i then enable my first date (greater than) filter it still works fine but as soon as i enable the second (less than) date filter nothing shows up. Simply entering the filter and not changing anything just hitting ok makes the filter work properly. The two date filters work independantly but not toget.

[Kenneth Hobs]

The CDate() should only be used on text data that is in date format. We can only guess at what does what.

If you are still stuck, post the workbook or a simple example of it.

[boc_est1986]

i had a quick look at cdate on the help section but still couldn't get it to work.
I think i applied the same principle but probably went the long way around. I copied in the text from each textbox (which has a note to use the format DD/MM/YYYY). I then converted the dates and the filter column into numerical date values. Still didn't work. As the issue was only affecting the todate i moved it to another cell. Still no luck so i removed the textbox2 from the form and copy and pasted textbox1. Now both methods are working, seems to be that there was an issue with something in the properties of the textbox.
Thanks for your help guys