Correctly formatting a date inserted from an input box

[dekueb]

Hello everyone. I need your help.

In fact I using this code (Thanks to walruseggman)

Please Login or Register  to view this content.

It works great but when the input box pops up to force a date in column B and because I live in Europe I enter the date dd/mm/yyyy and when I click ok it appears as mm/dd/yyyy in the cell. The cell is correctly formated. Is there a way to solve this?

For more information see this thread http://www.excelforum.com/excel-prog...two-dates.html

Thanks for your help

[Norie]

You could try using CDate or DateValue to convert the text date from the input box to a 'real' date.

Please Login or Register  to view this content.

[dekueb]

You could try using CDate or DateValue to convert the text date from the input box to a 'real' date.

Please Login or Register  to view this content.

Wow what a quick reply. Thanks.
Yes it takes it as a real date now, but, the date is accepted even if the time limit of 6 weeks is exceeded.

[Norie]

In this part of the code you don't check what's been input via the input box.

Please Login or Register  to view this content.

You probably want to repeat the check you have in the first If statement, and you might even want to check if a valid date has been entered.

Something like this perhaps, though I'm not sure if I've got the columns right.

Please Login or Register  to view this content.

[dekueb]

Thanks norie.
The problem now is that it return always the message even if the entered date is less than 6 weeks. Please don't forget that we enter the date always dd/mm/yyyy.

[dekueb]

Can someone help me with this?
Thx in adv.

[dekueb]

Ok seems to work now with this code don't know why but anyhow....but one fixed an other appear. When I click *cancel in the input box I recieve a error message. So whats the if statement?

Please Login or Register  to view this content.

[dekueb]

Can someone help me please

[dekueb]

No chance to get help?

[Norie]

You shouldn't put the result from the input box directly into the cell, or directly into the CDate function.

Instead put it in a variant variable, as I did in the code I posted, then it can be checked to see if a valid date has been entered and/or Cancel has been pressed.

[dekueb]

Thanks norie.
But the problem is, when useing your code, that it return always the message *Can not exceed 6 weeks* even if the entered date is less than 6 weeks as I already mentionned in post #5. Because no one respond to that post (don't see this as a complaint. I know we have all our private life) I tried to solve it by my one.

So thanks for any help.

[Norie]

Perhaps this will work.

Please Login or Register  to view this content.

If that doesn't work can you explain in words exactly what the code is supposed to do.

[dekueb]

Thanks norie.
It works now.