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

 

[JamesW]

Hi,

Try formatting the textbox in VBA:

Cells(1,1).value = Format(txtbox, "DD/MM/YYYY")

 

[Trevor G]

Stu,

You have to format the textbox on the form.

activecell.value=format(txtBox,"dd mm yyyy")

 

[Stu Dapples]

Cheers for the info guys but it doesn't seem to be doind anything...

Code is:

ActiveCell.Value = Format(txtBox, "dd mm yyyy")
If Date_Box.Value = "" Then
ActiveCell.Offset(-1, 0).Copy
ActiveCell.PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Else
ActiveCell.Value = Format(txtBox, "dd mm yyyy")
ActiveCell.Value = Date_Box.Value
End If

I entered the line you sent as above... Am I doind something wrong??

 

[JamesW]

Hi Stu,

Is your textbox called txtbox? You may need to change the name of it.

 

[Stu Dapples]

Hi Stu,

Is your textbox called txtbox? You may need to change the name of it.

Ah!! Got it now!! Many thanks for your help!!

 

[Stu Dapples]

OK, I dont get it!!

Using the code you sent, it does indeed put the date in the correct format, like this:

02 02 2011

but this string is not usable as a search function, excel wont even recognise it to give me a weeknum output for pivot table use later on....

AAAAAAAHHHHHH!!

 

[Stu Dapples]

Last ditch bump.... HELP!

 

[Norie]

The Format function returns text, if you want to put the date in a worksheet you should use DateValue to convert it to a 'real' date - what's in the textbox is text.

Then you should be able to use in calculations etc.

However I don't know if 02 02 2011 will be regarded as a recognised date format.

Is that how the date is formatted on the worksheet/form or is it how you want the date formatted?

 

[RoryA]

Use CDate:

activecell.value = Cdate(txtbox)