Setting a user's "Regional Options - Short Date" from Excel?

[Chrisso]

Hi All

Can I set the format of a user's "Regional Options - Short Date" from
Excel?

As far as I can work out it seems all you can do is ask Excel for a
short date and then it depends on the "Regional Options" of the user
whether a 2 digit (yy) or 4 digit (yyyy) year is displayed.

I have tried a custom format of "dd/mm/yy" but this just slips back to
a short "Date" cell and then displays based on the "Regional Options".

This caused me problems as my columns will not be correcly sized for
users with a short date of "dd/mm/yyyy" which is the standard Windows
setting. Column width is of the essence for presentation reasons.

In the past I have manually changed the Short Date configuration for
users but this does not scale of course and is completely impractical
for remote users.

Can I set the format of a user's "Regional Options - Short Date" from
Excel? Or should I just do an AutoFit on my date columns on
Worksheet_Activate?

Thanks for any advice,
Chrisso

 

[Guest]

Activecell.EntireColumn.Numberformat = "mm/dd/yy"


should display with a 2 digit date (make sure the values in the cells are
actually being stored as date serials and not strings). I would avoid
changing a users regional settings since regional settings are applied to
all applications - this would be poor behavior in a multitasking environment.

 

[Chrisso]

Yes - very good point Tom.

I have instead just programmed the date columns to autofit themselves
on worksheet activate.

Chrisso