Currency formatted fields and region settings

Currency formatted fields and region settings

Post by Jean-Guy M » Thu, 29 Jul 2004 08:40:05


Dans son message, < David Shaw > rivait :

|| Hi, could someone help me with a fields issue have in Word please.
|| I have a document with formfields in, the document was created on a
Windows/Word XP computer set
|| to English(United Kingdom) and to use formatted numbering ie 1,000.00 for
|| If you change the computer you are working on to French(Belgium) (They
show a thousand seperator
|| as a full stop and a decimal as a comma, the exact opposite of the UK
setting ie 1.000,00 for
|| 1000) in the control panel, I would expect the fields to swap the comma
and the full stop the
|| same way Excel does so a thousand is still a thousand, it does not, in
this instance 1.00
|| becomes .10, calculating fields is completely out of the question as they
go wrong as well, does
|| anyone know why Excel can do it correctly but Word cannot and what Word
is trying to do to come
|| up with the figures it does.
|| It appears that the form fields retain the british formatting inside
themselves and get into a
|| complete mess when the currency symbols in system are different.
|| Any help with this would be greatly appreciated.

I have faced this many times here in Canada where we have the English/French
numbering styles. By the way, are you sure that Belgium uses 1.000,00 for a
thousand? Shouldn't it be 1 000.00? (With a space as a decimal separator?)

As far as I can tell, you have two options:
One that involves VBA and that would set the fields format based on the
local machine regional settings whenever a document is opened. Lots of
testing and it can get hairy if you are not all that familiar with VBA. I
have never done it mainly because none of my client were ever willing to pay
for the time it would take. So I assume it can be done... You would need
stuff like:
Dim myDecSep As Variant
Dim myThouSep As Variant
Dim myListSep As Variant

myDecSep = Application.International(wdDecimalSeparator)
myThouSep = Application.International(wdThousandsSeparator)
myListSep = Application.International(wdListSeparator)

and stuff like:
Dim myFormfield As FormField

Set myFormfield = ActiveDocument.Content.FormFields(1)

With myFormfield
If .Type = wdFieldFormTextInput Then
With .TextInput
.EditType Type:=wdNumberText, Default:="", Format:= _
"# ##0,00"
' Here build the format string using the info from above, like:
' "#" & myThouSep & "##0"
' "#" & myThouSep & "##0" & myDecSep & "00 $"
' "0" & myDecSep & "00"

End With
End If
End With

But you have to unlock the form, save the field value before changing the
format so that you can set it back to its original value and you have to
watch out for the list separator in other fields.

The other solution is to temporarily set your machine to the Belgium
regional settings and create a localize version of the form. The drawback
with this is that forms cannot be sent to computers with different regional
settings for editing.

Jean-Guy Marcil - Word MVP
Word MVP site:

1. Excel's financial currency format vs. Access currency format

2. Format Currency field into zero filled text field

I have a field that can be blank or contain a currency amount. No matter
what the value is I want it to be a zero filled field. So for $9.99 I want
0000999 for a blank field i want 0000000 if it is $990.00 I want 0099000. It
seems I can get parts to work.

Format(Salary, "0000000") returns the amount rounded so I lose cents if I
use Format(Salary, "00000.00") i get it to work but have to manually remove

The other problem I am having is having it zero fill when no value

3. Setting company currency to foreign currency

4. Currency Datatype Really Good to Use For Non-currency Fields?

5. How to remove currency symbol from currency fields' labels?

6. Settings.settings file with settings for multiple regions

7. How to add new currency Symbol in Format/Cell/Currency

8. Conversion from currency value to currency text format

9. Setting null currency field to 0

10. Setting range of cells format to currency

11. Format String Currency with locale-specific settings

12. Regional Settings Currency Format

13. setting default value to $0.00 in a currency field

14. change regional Settings To UK currency format from a shared hosting server in Germany...