Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Form field formatting
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 |
#2
|
|||
|
|||
Ricki,
This worked for me: $### ### ###.00 Did you set the field type as a Number? |
#3
|
|||
|
|||
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" wrote in message oups.com... Ricki, This worked for me: $### ### ###.00 Did you set the field type as a Number? |
#4
|
|||
|
|||
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 :-( |
#5
|
|||
|
|||
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" wrote in message oups.com... 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 :-( |
#6
|
|||
|
|||
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 -- Greg Maxey/Word MVP A Peer in Peer to Peer Support Ricki Miles wrote: 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" wrote in message oups.com... 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 :-( |
#7
|
|||
|
|||
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" wrote in message ... 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 -- Greg Maxey/Word MVP A Peer in Peer to Peer Support Ricki Miles wrote: 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" wrote in message oups.com... 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 :-( |
#8
|
|||
|
|||
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/Word MVP A Peer in Peer to Peer Support Ricki Miles wrote: 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" wrote in message ... 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 -- Greg Maxey/Word MVP A Peer in Peer to Peer Support Ricki Miles wrote: 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" wrote in message oups.com... 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 :-( |
#9
|
|||
|
|||
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. -- Greg Maxey/Word MVP A Peer in Peer to Peer Support Ricki Miles wrote: 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" wrote in message ... 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 -- Greg Maxey/Word MVP A Peer in Peer to Peer Support Ricki Miles wrote: 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" wrote in message oups.com... 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 :-( |
#10
|
|||
|
|||
I just tested the macros and they seem to work perfectly. Thank you so much
for all your efforts!!! Ricki "Greg Maxey" wrote in message ... 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. -- Greg Maxey/Word MVP A Peer in Peer to Peer Support Ricki Miles wrote: 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" wrote in message ... 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 -- Greg Maxey/Word MVP A Peer in Peer to Peer Support Ricki Miles wrote: 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" wrote in message oups.com... 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 :-( |
#11
|
|||
|
|||
Ricki,
After conferring with some associates who are more knowledgeable on international issues, it appears that you can use Control PanelRegional 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. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retaining formatting on a single line after entering data in field | Page Layout | |||
Date formatting in text form field | Microsoft Word Help | |||
form and form field formatting | Microsoft Word Help | |||
Text Form Field Ref in Footer Won't Update on Screen | Microsoft Word Help | |||
Form Field Automation | Microsoft Word Help |