userform date will not recognise uk format dd/mm/yyyy

[cfinch100]

hi
have a userform that has a textbox for date , this is a search form when you enter a date 14/12/2012 it does not recognise it
the spreadsheet that i have is cell formated to this format but when i use form to search it is not recognised
also in the listbox below it uses us format aswell,if i change spreadsheet to us format it works ok albeit in us format,
i have read many posts and tried numerous remedies from forums all to no avail
having read these there seems to be a problem many people are having,datevalue does not work unless i am putting it in the wrong place
have heard you can change to a string and then convert but listbox is still wrong
surely there is someone out there with the expertise to have got around this problem
have uploaded the program
enter a date there are only 2 at minute 12/12/2012 and 14/12/2012 and press search ,
thats it and us format appears in listbox
any thoughts please
cheers colinHARNESS REGISTERcopymain.xls

[Norie]

Colin

When I search for 12/12/2012 it's found and the textboxes are filled out.

The listbox isn't filled though because it's row source is on Sheet2 which is empty.

[Norie]

I came up with this code which uses advanced filter.

It only works for the date at the moment but can be adapted.

Please Login or Register  to view this content.

[cfinch100]

hi
thats right but put in 14/12/2012 it is supposed to pick all them up, 12/12/2012 can be used both ways uk and us format
press search and with the date 14/12/2012 its not shown in listbox and if if does the format shows 12/14/2012
cheers colin

[Norie]

Colin

Did you try the code I posted?

It works for both 12/12/2012 and 14/12/2012 and displays the dates correctly, ie in UK format.

[cfinch100]

hi norie
where does this go listbox1 , userform initialise or somewhere else
cheers colin

[Norie]

It replaces the code for the search button.

Like I said it's only set up for date at the moment but that should be easy to change.

[Norie]

I've updated the workbook and the code I posted.

[cfinch100]

hi norie
yes got it now only prob is if you enter 14/12/2012 and double click on entry in the listbox it goes to debug/end instead of putting it up in search engine will
have to put the rest back in now for searching gpsh number name ect , the error points here[ListBox1.List = Application.Transpose(arrVals)] or later [.txtRecRow.Value = Trim(ListBox1.List(r, 6))] any ideas would be most welcome apart from the debug date is starting to work fine ,honestly been swapping things around all day and got no where
cheers colin

[Norie]

Colin

The code in the workbook will do a search on any of the fields, not just date.

I didn't touch the double click event - I never knew there was one.

The code errors here because there is no column 7.

Please Login or Register  to view this content.

I don't know why it would error here when the list was double clicked as this in the search button code.

Please Login or Register  to view this content.

[cfinch100]

hi norie
excellent work have now removed [.txtRecRow.Value = Trim(ListBox1.List(r, 6))] listbox1 double click and works brilliant , i knew some one had the skill to do
this many many thanks
colin

[Norie]

Colin

I updated the code to take care of the problem.

I think you might actually need that column as it shows the row on the original sheet the data comes from.

In fact it's used for the amend button.

[cfinch100]

hi norie
when you chosse a search listbox1 is populated when you click clear listbox1 does not how would you clear this at the same time please
cheers colin

[cfinch100]

hi
has bug in it will not load any ideas
cheers colin

[Norie]

Colin

It should be straightforward to clear the listbox, I'll have a look.

Don't quite follow that last post, what isn't loading?

[cfinch100]

hi norie
as it loads enable macro then debug appears error1004
"method worksheets global failed" but go into it and works fine obviously missing something
cheers colin

[Norie]

As what loads?

[cfinch100]

the program seems to be something to do with loading the userform sorted now , not sure what was happening but good now
thanks again
colin