Trying to format activecell.offset as date dd/mm/yyyy

[Raptor6L]

Hi all

First post, so play nice.

I've got a macro for a spreadsheet that help control a part of an inventory. The specific problem i have is when it it copying a date (and there a three cell that will possible have a date in it) from the userform into the database, it doing the thing where it copies it over into US date format.
I've done plenty of Googling, but haven't found anything that will do the job yet. I think i can be done but i just don't know enough about VBA to get the formatting of the code right.

So here we go:

Please Login or Register  to view this content.

So before this i have snumber 1 thru to 15 referring to the different cells on the userform.
The main one i'm trying to get sorted is snumber(7) as it is the main one. The one that will have a date in it each and every time.
So, trying to get the format of dd/mm/yyyy. The originating cell is dd/mm/yyyy, but i don't think that make a difference, does it?
I have a reversal of the above code as well where you enquire if a certain number is there and if it is it bring back all the revelant fields to the userfrom, so i'll need a fix for

Please Login or Register  to view this content.

as well.

I'm not sure what other info to give you guys and gals? Feel free to ask!!!

I can post up the whole shooting match if you want, but it's long, and pretty sure this is the area that needs focus. Happy to be proved wrong though.

Cheers
Chris

[Raptor6L]

Trying

Please Login or Register  to view this content.

gets me close. Using the debugger i can see snumber(7) is 01/10/2010, and the ActiveCell.Offset(z, 6).NumberFormatLocal wants to put it in as dd/mm/yyyy but won't actually show me the date and when i run it it return the error "Unable to set the NumberFormat property of the Range class”.

[Raptor6L]

Anyone? Help?

[Raptor6L]

Ok, so we'll try this another way. I stripped back the code to a one cell per sheet event to make testing a bit easier.
Paste the following code into a module:

Please Login or Register  to view this content.

In cell A1 of Sheet1 type in a date like 02/10/2012. dd/mm/yyyy. Hit the macro and the date will appear in cell A2 of Sheet2, but it will appear as 10/02/2012. The changing of the date format from dd/mm to mm/dd is the crux of my problem.

The delete date was just to cut down on the mouse clicks.

I'm hoping someone here is smarter than I and also willing to give it a go.

Cheers
Chris

[Raptor6L]

Ok, so it seems no here is smarter than me!

The solution was soooo bloody simple! All i needed to do was ad a 2 to the .Value and job's done.

Please Login or Register  to view this content.

Thanks to everyone that helped............wait a minute..........

Chris

[JosephP]

Glad you got it fixed, though your post implied you were returning data from a userform control to a cell for which value2 won't help-you'd need CDate-unless you entered the date in the control as a number and not a date. I mention it in case it helps ;-)

[Raptor6L]

Glad you got it fixed, though your post implied you were returning data from a userform control to a cell for which value2 won't help-you'd need CDate-unless you entered the date in the control as a number and not a date. I mention it in case it helps ;-)

Sheet1 in the Workbook is called "Userform". It's not a Userform Control.
The example i posted for testing is accurate in everyway except it's on a smaller scale. No mention of a userform control there.

A bit frustrating that a single character was all that was needed. I thought i had tested this method already but, i either got side tracked and didn't get to it, or I've tried it and something else was wrong. I dunno...... but doesn't matter now, it's all good.
This has been plaguing me for about 3 months, until a few days ago, so hopefully this helps a few other people out there!

Cheers
Chris

[JosephP]

Sheet1 in the Workbook is called "Userform". It's not a Userform Control.
The example i posted for testing is accurate in everyway except it's on a smaller scale. No mention of a userform control there.

your original post said "The specific problem i have is when it it copying a date (and there a three cell that will possible have a date in it) from the userform into the database" and there was no sample workbook to make clear that you did not mean an actual userform. ;-)

all I'm saying is that the clearer your post is the more likely you are to get assistance although replying to your own post within a few hours also removes it from the 'posts with no replies' list that many regulars seem to use when looking for questions.

in any event thank you for posting your solution for others' benefit :-)