not as easy as it may sound because I currently have 2 columns in dat
format of dd-mmm-yy i.e. 05-Jan-04 and using cdate to convert from th
text box only seems to put it into dd/mm/yyyy format. I would reall
like to keep the dd-mmm-yy format that is used in the columns as i
makes it easier to see than 05/02/2004 when you have a big list o
dates.

So I need a way of converting the text the user types into the text bo
into date format dd-mmm-yy so that i can use autofilter to search it

--
Message posted from http://www.ExcelForum.com

neo,

Try something like

myDateStr = Format(CDate(Textbox.Text), "dd-mmm-yyyy")

HTH,
Bernie
MS Excel MVP

"neowok " wrote in message
...
not as easy as it may sound because I currently have 2 columns in date
format of dd-mmm-yy i.e. 05-Jan-04 and using cdate to convert from the
text box only seems to put it into dd/mm/yyyy format. I would really
like to keep the dd-mmm-yy format that is used in the columns as it
makes it easier to see than 05/02/2004 when you have a big list of
dates.

So I need a way of converting the text the user types into the text box
into date format dd-mmm-yy so that i can use autofilter to search it.


---
Message posted from http://www.ExcelForum.com/

ok ill give it a try monday

thank

--
Message posted from http://www.ExcelForum.com

just tried that but isnt working, code i used was

Private Sub CommandButton4_Click()
Dim mydatestr As String
mydatestr = Format(CDate(TextBox2.value), "dd-mmm-yyyy")
Range("J23").Value = mydatestr
Me.Range("mytables").AutoFilter 6, mydatestr, _
Operator:=xlAnd
End Sub

only way ive got it to return a result so far is by

Me.Range("mytables").AutoFilter 6, cdate(textbox2.text), _
Operator:=xlAnd but i had to convert the column to the first dat
format *dd/mm/yyyy which seems to be what cdate converts to

--
Message posted from http://www.ExcelForum.com

Neowok,

Your code worked fine for me if:

1) the entire column J was formated custom dd-mmm-yyyy
2) I removed the Me. before the autofilter method.

HTH,
Bernie
MS Excel MVP

"neowok " wrote in message
...
just tried that but isnt working, code i used was

Private Sub CommandButton4_Click()
Dim mydatestr As String
mydatestr = Format(CDate(TextBox2.value), "dd-mmm-yyyy")
Range("J23").Value = mydatestr
Me.Range("mytables").AutoFilter 6, mydatestr, _
Operator:=xlAnd
End Sub

only way ive got it to return a result so far is by

Me.Range("mytables").AutoFilter 6, cdate(textbox2.text), _
Operator:=xlAnd but i had to convert the column to the first date
format *dd/mm/yyyy which seems to be what cdate converts to.


---
Message posted from http://www.ExcelForum.com/

ahh thanks, ill try removing the ME then (dont know exactly what that
for anyway

--
Message posted from http://www.ExcelForum.com