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

 

[Stu Dapples]

The Format function returns text, if you want to put the date in a worksheet you should use DateValue to convert it to a 'rea' 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?

I'm not sure! The user enters a date onto the form, once all the required data is entered, he hits a "save" button which unloads the form into the spreadsheet. The data that the user inputs is currently being formatted to a string which I dont want, I just want the value of the text box to be formatted to date format dd/mm/yyyy as if it were entered straight into the sheet in a cell...

I tried formatting the textbox but I dont really know where to put the code, I tried putting it on the sub for dropping the data in but it gets either ignores it or errors out for compile errors....

Sorry to be a bit vague but I just learning all this and tried to make life easier by using a userform!!

Thanks for your help

Stu

 

[JamesW]

Hi Stu,

Did you try Format(txtbox, "DD/MM/YYYY") as I posted in my solution? That should work for calcs.

 

[Norie]

Stu

What code are you using to put the data onto the worksheet?

How have you formatted the cells in the worksheet the data is going?

Try not formatting the cells at all, or formatting them after you've put the data in them.

 

[Stu Dapples]

JamesW - Yes, I did try the code you sent, the date is being entered into the cell as (eg) 02 03 2011, while this looks fine, I cant use this format for calcs (such as weeknum) or sorting as Excel doesnt seem to like the format, not sure why?

Norie - I am using activeCell.Value = Date_Box.Value

After offsetting into the required row/ column, I tried using:

ActiveCell.Value = Date_Box.Text but it seems to make no difference....

I have formatted the column to Date Format (dd/mm/yyyy) but it only works if I physically type the date in, if it comes from the userform, it reverses the day/ month numbers...

I have a workaround in place at the moment which is fine for my use but if anyone else uses it, it may get messy. I have set up an IF statement on the date value that if there is a date in the previous record, copy it and put it into the current record. With this I just manually enter the date into the sheet each time it changes and leave it blank on the userform...

Sorry to be a pain fellas, many thanks for your support

Stu

 

[RoryA]

Again: use CDate.

 

[Stu Dapples]

Again: use CDate.

I know I am making myself look a real amatuer here (which is only fair since I am!!) but where do I use that command? I did take note of your suggestion but it either errors out or does nothing, I am guessing it is me and not VB that is getting it wrong!

Cheers

Stu

 

[RoryA]

How and where did you use it and what was in the textbox at the time?

You'll get better help if you respond to all suggestions.

 

[Stu Dapples]

How and where did you use it and what was in the textbox at the time?

You'll get better help if you respond to all suggestions.

Apologies, I went off down a dead end for a while!!

I have found why it didn't work, because of of the IF statement I put in as a workaround, the code ran on the section on which I didn't apply the Cdate command, my fault!

I have now put it in as follows:

If Date_Box.Value = "" Then
ActiveCell.Offset(-1, 0).Copy
ActiveCell.PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Else
'ActiveCell.Value
ActiveCell.Value = CDate(Date_Box)
End If

which has done the trick! Again, apologies for not sorting that earlier but many thanks for not losing patience with me! I am on a steep learning curve and sometimes the curve goes down as well as up....

 

[Norie]

So you never actually tried using the function DateValue?

By the way what are you actually trying to do with the code you posted?

It looks like you want to use some sort of default value for the date if the user hasn't entered something on the userform?

 

[Stu Dapples]

So you never actually tried using the function DateValue?

By the way what are you actually trying to do with the code you posted?

It looks like you want to use some sort of default value for the date if the user hasn't entered something on the userform?

I did try it but it gave me the odd date format I mentioned in previous posts... The idea of the bit of code I posted is that there is a sheet from teh department filled in each day, there can be between 10 and 30 records. To save filling in the date for each row, the user fills it in for the first record then tabs past it for each subsequent record on that day (the code grabs the previous date value and copies it into the relevant cell)
I just got fed up with typing the date each time! I did need it in the correct format though!!

Again, many thanks to all those who contributed, much appreciated, I will no doubt be back to pick your brains in the near future (as well as try and help those asking - I am quite pleased I have been able to answer a few posts too )