About Us

James Price at Premier

Thanks Rick. You're a star

James

"Rick Rothstein" wrote:

Thanks for this. A couple of points though. I want to be able to identify
the local international setting so when the user initialises the form and
sees the text box they see the text 'dd/mm/yy' or 'mm/dd/yy' to enter the
date.

Assuming the locale always uses m, d and y for the month, day and year date parts of the date format pattern string, you can use this function to return the text string you want to display in the TextBox...

Function DateFormat() As String
DateFormat = CStr(DateSerial(2003, 1, 2))
DateFormat = Replace(DateFormat, "2003", "yyyy")
DateFormat = Replace(DateFormat, "03", "yy")
DateFormat = Replace(DateFormat, "01", "mm")
DateFormat = Replace(DateFormat, "1", "m")
DateFormat = Replace(DateFormat, "02", "dd")
DateFormat = Replace(DateFormat, "2", "d")
DateFormat = Replace(DateFormat, MonthName(1), "mmmm")
DateFormat = Replace(DateFormat, MonthName(1, True), "mmm")
End Function

To use this function, you would use a statement like this...

TextBox1.Text = DateFormat

If your locale can use different letters for the month, day and year date parts, then this modification to the above should work...

Function DateFormat(TheDate As Date) As String
DateFormat = CStr(DateSerial(2003, 1, 2))
With Application
DateFormat = Replace(DateFormat, "2003", String(4, ..International(xlYearCode)))
DateFormat = Replace(DateFormat, "03", String(2, ..International(xlYearCode)))
DateFormat = Replace(DateFormat, "01", String(2, ..International(xlMonthCode)))
DateFormat = Replace(DateFormat, "1", .International(xlMonthCode))
DateFormat = Replace(DateFormat, "02", String(2, ..International(xlDayCode)))
DateFormat = Replace(DateFormat, "2", .International(xlDayCode))
DateFormat = Replace(DateFormat, MonthName(1), String(4, ..International(xlMonthCode)))
DateFormat = Replace(DateFormat, MonthName(1, True), String(3, ..International(xlMonthCode)))
End With
End Function

--
Rick (MVP - Excel)

"James Price at Premier" wrote in message ...
Thanks for this. A couple of points though. I want to be able to identify
the local international setting so when the user initialises the form and
sees the text box they see the text 'dd/mm/yy' or 'mm/dd/yy' to enter the
date.

The other problem is in Germany for dates they use 'dd.mm.yy' but looking at
your example Excel doesn't recognise say '.' as part of a date.

Cheers

James

"Chip Pearson" wrote:

The following function will format a date to the settings for any
locale.

Function FormatDate(TheDate As Date) As String

Dim DateSep As String
Dim sMM As String
Dim sDD As String
Dim sYY As String
Dim S As String

With Application
If .International(xlDayLeadingZero) Then
sDD = String(2, .International(xlDayCode))
Else
sDD = String(1, .International(xlDayCode))
End If
If .International(xlMonthLeadingZero) Then
sMM = String(2, .International(xlMonthCode))
Else
sMM = String(1, .International(xlMonthCode))
End If
If .International(xl4DigitYears) Then
sYY = String(4, .International(xlYearCode))
Else
sYY = String(2, .International(xlYearCode))
End If
DateSep = .International(xlDateSeparator)

Select Case .International(xlDateOrder)
Case 0 'm/d/y
S = sMM & DateSep & sDD & DateSep & sYY
Case 1 'd/m/y
S = sDD & DateSep & sMM & DateSep & sYY
Case 2 'y/m/d
S = sYY & DateSep & sMM & DateSep & sYY
End Select
End With
FormatDate = Format(TheDate, S)
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Fri, 3 Apr 2009 07:57:11 -0700, James Price at Premier
wrote:

I want to show the default short data type in a cell but it needs to
automatically know what the internation settings are. For example, if I'm in
the UK the text box inside the form will show 'dd/mm/yy', if US 'mm/dd/yy'
and so on.

Many thanks

James

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Date format changes when inputed from user form	Lynz	Excel Discussion (Misc queries)	16	December 13th 08 11:27 PM
Texttocolumns and Date Format using Macro - UK International setti	KenY	Excel Programming	6	October 17th 07 02:33 PM
visual basic user form date format dd/mm/yy not mm/dd/yy	DarrenO	Excel Discussion (Misc queries)	1	March 28th 07 01:56 AM
date format in user form	TUNGANA KURMA RAJU	Excel Programming	1	August 7th 06 06:17 AM
how to format a date/validate for a text box entry on a user form	Tom Ogilvy	Excel Programming	3	June 1st 05 05:06 PM

Menu

About Us