Form field formatting

[Ricki Miles]

I am using Word XP. Is there a way to format a number form field in a
protected form differently than the formats provided in the form field
options? These numbers will have to be calculated.

I would like to format the number with a dollar sign, but with a space
rather than
a comma between the thousands and hundreds and the millions and thousands.

I posted this question previously and the response was to change the formats
given in the number field formatting. I tried # ###.00 which resulted in a
number with no space between thousands and hundreds. Also, I tried 0 000.00
which also resulted in a number with no space between thousands and
hundreds. I tried #" "###.00 which gave me quotation marks in the result.

Thanks for the help,

Ricki Miles

 

[Greg]

Ricki,

This worked for me:

$### ### ###.00

Did you set the field type as a Number?

 

[Ricki Miles]

Hi Greg,

I have tried your method. It works if you use 9 digits and 2 decimals, but
not for numbers of 4 digits, etc. I need the space instead of the comma as
it is a corporate standard. I have not been able to put in a number format
that will work with any amount of digits and still calculate.

Please let me know if you find anything that will work. Thanks,

Ricki

 

[Greg]

Ricki,

Now I see your problem, I just selected the standard format

$#,##0.00;($#,##0.00)

Replaced the commas with spaces:

$# ##0.00;($# ##0.00)

and ran some test.

1 returns $1.00
1234 returns $1 234.00

however the calculations are treating the 1 234 as two numbers. If I
add

$ 1 234.00 and $2.00 I am getting $237.00 or 1 + 234 + 2

I am stumped right now :-(

 

[Ricki Miles]

That's exactly my problem. Can you think of another solution? The client
needs a template that is "locked down" so users cannot changes existing text
and formatting - thus, the protected form fields. But the number corporate
standard is a space instead of a comma and the fields need to calculate
properly.

I have tried many things, but nothing seems to work. Formatting can be
customized like this in Excel. Do you think this form should have an on
exit macro that takes the numbers from Word into Excel, formats them and
sends them back? I wouldn't know how to write the macro to go from one
program to another. I would be happy to make the form with macrobuttons and
empty fields (control+F9), but then there is no protecting the text from
users who want to change it.

Thanks for the help,

Ricki

 

[Greg Maxey]

Ricki,

I am totally stumped. I even tried changing the number separator in Control
Panel> Regional and Language settings to a space vice a comma but I still
can't get it to work. I am working with Word2003 now (Word2000 earlier) and
now I can't even get 1234 to look like $1 234.00 it comes out $ 1 2.34 :-(

I tried using VBA on exit to take the formfield result and format it and
feed it back, but this doesn't work either:

Text1.Result = Format(Text.Result, "$# ###.00")

If I think of anything I will post back

 

[Ricki Miles]

Thanks for all you efforts, Greg. I wish there was another way to have text
protected in Word - Form fields are very limiting.

Ricki

 

[Greg Maxey]

Ricki,

This is ugly, but close. I ran a test on two formfields and summed the
results. Probably to much trouble to pursue.

Set your formfields as regular text
Run this macro on entry
Sub GetIndex()
Dim oDoc As Document
Set oDoc = ActiveDocument
oDoc.Variables("Index").Value = oDoc.Range(0,
Selection.Bookmarks(1).Range.End).Bookmarks.Count
End Sub

Run this macro on exit
Sub Test()

Dim oDoc As Document
Dim i As Long
Set oDoc = ActiveDocument
i = CStr(oDoc.Variables("Index").Value)
Dim a As String
Dim b As String
oDoc.Variables("Text1").Value = oDoc.Bookmarks("Text1").Range.Text
oDoc.Variables("Text2").Value = oDoc.Bookmarks("Text2").Range.Text
a = oDoc.Variables("Text1").Value
b = oDoc.Variables("Text2").Value
Do
a = Mid$(a, 2)
Loop Until IsNumeric(Left$(a, 1))
Do
b = Mid$(b, 2)
Loop Until IsNumeric(Left$(b, 1))
oDoc.Variables("Result1").Value = Format(Val(a) + Val(b), "$## ### ##0.00")
oDoc.FormFields(i).Result = Format(oDoc.FormFields(i).Result, "$## ###
##0.00")
oDoc.Fields.Update
End Sub

My brain is fried and I can't think of anything else.

 

[Greg Maxey]

Ricki,

OK this appears to be dead on, but still ugly. The close before was that I
couldn't get the dollar sign to close up on the left hand digit. The
solution is to put the dollar sign as boiler plate text in the document
i.e.,

${FormText} and then Trim the formatted values.

With that we can eliminate the two Do Looops. I also realized that setting
the bookmarktext values as docVariables was redundant. We can also use a
third regular text formfield (entry disabled) to display the result. A
cleaner version but still alot of work is:

Using 3 regular text formfields Text1, Text2 and Text3

Run on Entry Text1 and Text2
Sub GetIndex()
'Gets the index value for use in the Exit event
Dim oDoc As Document
Set oDoc = ActiveDocument
oDoc.Variables("Index").Value = _
oDoc.Range(0, Selection.Bookmarks(1).Range.End).Bookmarks.Count
End Sub

Run on Exit Text1 and Text2
Sub CalcAndFormat()
Dim oDoc As Document
Dim i As Long
Dim a As String
Dim b As String

Set oDoc = ActiveDocument
i = CStr(oDoc.Variables("Index").Value)

oDoc.FormFields(i).Result = _
Trim(Format(oDoc.FormFields(i).Result, "### ### ##0.00"))
a = oDoc.Bookmarks("Text1").Range.Text
b = oDoc.Bookmarks("Text2").Range.Text
oDoc.FormFields("Text3").Result = _
Trim(Format(Val(a) + Val(b), "### ### ##0.00"))

End Sub

You might want to put some test for numeric input in the exit event.

Let me know how you do. Good luck.

 

[Ricki Miles]

I just tested the macros and they seem to work perfectly. Thank you so much
for all your efforts!!!

Ricki

 

[Greg]

Ricki,

After conferring with some associates who are more knowledgeable on
international issues, it appears that you can use Control
Panel>Regional and Language Settings to set the number and currency
separator to a space and the formfield and calculation field will work.
I just confirmed this in Word2000 but didn't get it to work initially
with Word2003 at home before I left for work. Of course the user would
have to set his/her settings to match. If what you have is working it
may be better to leave as is.