VB for correct date format from User Form

[Stu Dapples]

So I now have a nice little user form which will cut down data entry time by vast amounts....

Except the box for date is returning the data in the wrong format...

If I enter the date on the userform as 4/2/11
It is entered into the appropriate cell as 2/4/11 which is going to make analysis a bit difficult!

I have formatted the cells to UK date convention (dd/mm/yy) but it doesn't seem to make any difference...

The data box on the userform is a stock text box btw, not sure if this has any bearing on the situation?

Any advice much appreciated...

Stu

 

[Norie]

Stu

Can I ask, just out of curiousity really, how you tried DateValue?

 

[Stu Dapples]

Stu

Can I ask, just out of curiousity really, how you tried DateValue?

As I said in last post, I did try it and it worked to a fashion, it just gave me the odd date format (eg 02 03 2011) which for some reason Excel was viewing as a text string (I think!) when I tried to apply a WEEKNUM() function to it on the spreadsheet, it came up with a #VALUE! error... As RoryA mentioned, it may have been because I had already set the cell format to dd/mmm/yy and then dropped the value in, maybe a sort of double formatting thing? Not sure...

Is there something I am missing?

Many thanks,

Stu

 

[Norie]

Stu

All I'm wondering is how exactly you used it, because as far as I know it should work in the same way as CDate.

ActiveCell.Value = DateValue(TextBox1.Value)
 
'Or
 
ActiveCell.Value =Iif(Textbox1.Value<>"", DateValue(Textbox1.Value), ActiveCell.Offset(-1).Value)

 

[Stu Dapples]

Stu

All I'm wondering is how exactly you used it, because as far as I know it should work in the same way as CDate.

ActiveCell.Value = DateValue(TextBox1.Value)
 
'This ^^^ is how I did it originally, it gave me the spaced/ text string date issue I mentioned...
 
 
ActiveCell.Value =Iif(Textbox1.Value<>"", DateValue(Textbox1.Value), ActiveCell.Offset(-1).Value)
'I didn't try it quite like this ^^^ I used the line above and added into my original IF statement code

Does that make sense? I do like the Iif though, is that a shortened version of an IF statement (noted there is no ELSE or ENDIF statements)
Cheers

Stu