Re: override locale computer settings

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dave Peterson (ec35720_at_netscapeXSPAM.com)
Date: 02/18/05 

Date: Thu, 17 Feb 2005 18:00:52 -0600

For me (USA settings), Jim's code put:

y
m
d

In A1:A3

Then Jim's formula:
=TEXT(F2,REPT(A2,2)&"."&REPT(A3,2)&"."&REPT(A1,4))
uses those codes to build the text string

So Rept(A2,2) will look like: mm
   Rept(A3,2) will look like: dd
   Rept(A1,4) will look like: yyyy

or if I substituted (manually), I'd get:
=TEXT(F2,"mm.dd.yyyy")

=======
So you could make that macro run each time you open the workbook--just name it
auto_open().

If you put those strings on a separate work*** (hidden?????), you can use them
in your formula:

=TEXT(F2,REPT(Sheet2!A2,2)&"."&REPT(Sheet2!A3,2)&"."&REPT(Sheet2!A1,4))

(I used Sheet2 as my hidden worksheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Make sure you have a worksheet named Sheet2. Then close (and save) that
workbook and reopen it.

Now try the variation of Jim's work*** formula as a test.

n.almeida wrote:
>
> Hi Jim
> Thanks for your answer.
> Unfortunately I am not familiar with macros. I would appreciate if you could
> direct me a bit on this.
> However, from your answer I think you misunderstood (or I misunderstood your
> reply): I do not want to have the the format of the region of the user, quite
> the opposite I want to override them in order to have the same format
> independently of the region odf the user.
>
> "Jim Rech" wrote:
>
> > You should avoid like the plague any functions that use region-specific
> > strings like TEXT. Otherwise you'll have to find a way of determining
> > dynamically the format codes you need in the region of the user.
> >
> > One way is with a macro. Just as an example, this macro returns the three
> > date codes of the current region:
> >
> > Sub ListDateCode()
> > Range("A1").Value = Application.International(xlYearCode)
> > Range("A2").Value = Application.International(xlMonthCode)
> > Range("A3").Value = Application.International(xlDayCode)
> > End Sub
> >
> > Then you could refer to these results like this:
> > =TEXT(F2,REPT(A2,2)&"."&REPT(A3,2)&"."&REPT(A1,4))
> >
> > --
> > Jim Rech
> > Excel MVP
> > "n.almeida" <nalmeida@discussions.microsoft.com> wrote in message
> > news:CCB4AF79-9DE3-40E6-B7EC-F1D6244713F5@microsoft.com...
> > |I appreciate any help. This is a problem I posted yesterday probably
> > without
> > | being very clear.
> > |
> > | How can I override the Windows Regional settings so that my Excel File
> > looks
> > | the same in a computer in Italy and in the Netherlands without forcing the
> > | user to change the Locale Regional settings?
> > | This affects the Date and the Time format, specially within formulas.
> > |
> > | Also it seems that the locale setting on Cell Format only works if the
> > | locale is NOT the default.
> > | i.e. A file with the default in UK Eng. and a cell date in Italian and
> > | another in UK Eng, will have the cell in Italian on a Dutch computer but
> > the
> > | UK one will be turned into Dutch.
> > |
> > | Besides, this setting does not work within a cell.
> > | i.e. the formula TEXT(E12,"*yymmdd")&"-Blabla" will look like
> > 050216-Blabla
> > | on a UK Eng, but will look yy0216-Blabla on a Dutch or Italian computer
> > |
> > | I appreciate any help. This is a problem posted yesterda
> >
> >
> > 

-- 
Dave Peterson
Quantcast