Dates after 20029 ???

[Enkay62]

Hi.
Here is my problem.
My o.s. is windows '98.
In vba for excel 2000 I've a form with a textbox in which users have to insert dates.
I've formatted that textbox dd/mm/yyyy, in order to avoid problems with some years (i.e. with a "Short date" format, /45 is considered 1945 and not 2045...and so on).
The result is correct. All the years are correctly recognized.
The problem is that although the format I built for the textbox (in initialize and in exit events), when I exit the textbox after wtiting i.e. 10/10/2004, format becomes again "Short date" (i.e. 10/10/04).
This happens only if the year is before 2030.
i.e . If I write 10/10/2029, it becomes 10/10/29.
On the contrary, if I write 10/10/2030, it remains 10/10/2030.
How can I get the date format to remain as I programmed it (/yyyy)?
Thank you in advance for your suggestions.
Regards.
Nick.

 

[PHV]

Have you take a look at the regional settings, in Config Panel ?

Hope This Help
PH.

 

[TonyJollans]

Hi Enkay62,

I think Excel uses the Windows regional Settings for all this - check under the Date tab.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Enkay62]

Hi TonyJollans & phv.
Thank you for answering.
I checked regional settings, they are ok.
The problem must be another.
The problem, in fact, occurs just for years before 2030 (probably to avoid ambiguity wirh short dates, windows consider all the years before '30 as 20xx, and all the years after '30, as 19xx).
I suppose that there's no reason related to regional settings for changing to "Short date" just after 2030.
I don't know what to do because the program I'm creating must have to do with very remote years (forward and backward) so I cannot have any risk of ambiguity (if users insert 10/10/31 they mean 10/10/2031 but, if I don't solve the problem, excel will consider it 10/10/1931......).
Any other suggestion will be appreciated.
Thank you.
Nick.

 

[combo]

Hi,
I think that the 'Text' property could be better than 'Value' to handle dates. You can use Format function to set date in the text box. Exit event procedure should test string for valid date.
I wonder if it is possible to format textbox to display and interpret date/number in a given format. DateValue function interprets 3/1/2003 as first of March and AFAIK this cannot be overcome.

combo

 

[TonyJollans]

Hi Enkay62,

Can I take a step back here and ask what exactly you mean when you say you format the textbox?

Textboxes hold TEXT. Depending on what you do with that text it may be interpreted in various ways but, as far as I know, you cannot format a textbox per se, so what is it that you are doing? Can you post code and/or more details, please.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Enkay62]

Here I am , Tony.
I do apologize.
You were right.
It's a matter of regional settings.
My "Short date" settings are .../yy and not .../yyyy, so excel recognizes the differences but shows always a short date.
So my new problem is :
is it possible to change regional settings via vba?
Thank you again, Tony.
Bye.
Nick.

 

[SkipVought]

Nick,

Excel has the smarts to convert the STRING 10/10/30 to 11241 AND format the cell according to your Regional Setting short date.

If your users INTEND that 10/10/30 is a 2030 date, then your TextBox logic needs to convert the STRING 10/10/30 to 10/10/2030 and assign that string to the cell. THEN Excel will convert the string 10/10/30 to 47766 and format accordingly.





Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Enkay62]

Hi Skip,
as a matter of fact, I realized that if users type the whole year (i.e. 2020) in any case excel distinguish the century correctly.
The only problem is what it displays and it depends on "Short date" settings.
So: is there a way to change those settings via vba?
Thank you very much.
Nick.

 

[SkipVought]

1. I am not suggesting that the USER be required to enter a full year. That's what YOUR PROGRAM need to do.

2. If YOUR PROGRAM assembles the date value in the form dd/mm/yyyy and assigns that to the cell, YOUR PROGRAM can also format that cell as "dd/mm/yyyy" and that's what you'll see. NO NEED TO CHANGE REGIONAL SETTING!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Enkay62]

Skip.
I understand what you say but, maybe, I didn't explain correctly what my problem is.
So: I've some textboxes in a form. Some of these textboxes receive data in date format from some cells.
Dates in cells are correctly formatted and displayed (dd/mm/yyyy).
In initialize and exit event of my form I put code to format the textboxes (dd/mm/yyyy).
Here is were it doesn't work.
Dates in textboxes are still in "Short date".
Perhaps I put the code in the wrong events or my code is wrong?
Code (in "Initialize" and "Exit" events) :
Userform1.textbox1.value=Format_(Userform1.textbox1.value,"dd/mm/yyyy&quot
I also tried replacing "Value" with "Text", but didn't work anyway.
Were am I wrong?
Thank you.
Nick.

 

[TonyJollans]

Hi Enkay62,

Yes, it IS possible to change the Regional Settings via VBA but it involves several Windows API calls and I would not recommend it. Regional settings are not Excel-specific and Users may have them set the way they have for all sorts of reasons.

The thing to do is, as Skip suggests, explicitly format your cells (I am assuming it's cells we're working with here) as you want them, rather than using a format which the User can tailor.



Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Enkay62]

Tony,
You are right.
As you can see in the above post, the problem is not with cells, but with textbox.
Is there anything wrong in my code (Initialize and exit events)?
Userform1.textbox1.value=Format_(Userform1.textbox1.value,"dd/mm/yyyy&quot
thank you.
Bye
Nick

 

[SkipVought]

and...

the other salient point is that your TextBox contains TEXT not DATE. Keep in mind that Excel Dates are NUMBERS like 11241 or 47766 that can be formatted as "10/10/1930" or "10/10/2030" respectively.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

I personally hate having to validate dates from a textbox
I find it easier to use 3 dropdown boxes and then combine the day, month and year together AFTER the user has selected them. You need a bit of logic so that they can't choose 31 days in june etc but that isn't too hard and IMHO is far better than trying to change regional settings

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

AMEN!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi Nick,

Are you saying that, after you have done that formatting in the textbox exit event that it shows in the textbox with a 2-digit year?

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.