Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Method for displaying dollar amount as written on bank check
Is there a formula or some method to display a dollar amount that is entered
in a form field as if it were being written on a bank check. For example: I enter 100.25 and it would display One Hundred Dollars and 25/100. |
#2
|
|||
|
|||
The \* DollarText formatting switch (with \* Caps) will give you "One
Hundred and 25/100." You will have to add "Dollars," which should come at the end (25 cents = 25/100 dollars). -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA Word MVP FAQ site: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. "samandmonty" wrote in message ... Is there a formula or some method to display a dollar amount that is entered in a form field as if it were being written on a bank check. For example: I enter 100.25 and it would display One Hundred Dollars and 25/100. |
#3
|
|||
|
|||
On a form field?
-- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Suzanne S. Barnhill wrote: The \* DollarText formatting switch (with \* Caps) will give you "One Hundred and 25/100." You will have to add "Dollars," which should come at the end (25 cents = 25/100 dollars). "samandmonty" wrote in message ... Is there a formula or some method to display a dollar amount that is entered in a form field as if it were being written on a bank check. For example: I enter 100.25 and it would display One Hundred Dollars and 25/100. |
#4
|
|||
|
|||
It isn't easy, but by putting some of Doug Robbin's VBA to work, it can
be done: Set the macro FormatCurrency to run on exit from the field. This example formats the formfield Text1 Sub FormatCurrency() Dim MyNumber MyNumber = ActiveDocument.FormFields("Text1").Result ActiveDocument.FormFields("Text1").Result = ConvertCurrencyToEnglish(ByVal MyNumber) End Sub Function ConvertCurrencyToEnglish(ByVal MyNumber) Dim Temp Dim Dollars, Cents Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " MyNumber = Trim(Str(MyNumber)) DecimalPlace = InStr(MyNumber, ".") If DecimalPlace 0 Then Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2) Cents = ConvertTens(Temp) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber "" 'convert last 3 digits to English Dollars Temp = ConvertHundreds(Right(MyNumber, 3)) If Temp "" Then Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) 3 Then 'remove last 3 comverted digits MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop 'clean up dollars Select Case Dollars Case "" Dollars = "NoDollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select 'clean up cents Select Case Cents Case "" Cents = " And No Cents" Case "One" Cents = " And One Cent" Case Else Cents = " And " & Cents & " Cents" End Select ConvertCurrencyToEnglish = Dollars & Cents End Function Private Function ConvertHundreds(ByVal MyNumber) Dim Result As String If Val(MyNumber) = 0 Then Exit Function 'append leading zeros to number MyNumber = Right("000" & MyNumber, 3) 'do we have hundreds place digit to convert? If Left(MyNumber, 1) "0" Then Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred " End If 'do we have tens place digit to convert? If Mid(MyNumber, 2, 1) "0" Then Result = Result & ConvertTens(Mid(MyNumber, 2)) Else 'if not, then convert the ones place digit Result = Result & ConvertDigit(Mid(MyNumber, 3)) End If ConvertHundreds = Trim(Result) End Function Private Function ConvertTens(ByVal MyTens) Dim Result As String 'is value between 10 and 19? If Val(Left(MyTens, 1)) = 1 Then Select Case Val(MyTens) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else Select Case Val(Left(MyTens, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select 'convert ones place digit Result = Result & ConvertDigit(Right(MyTens, 1)) End If ConvertTens = Result End Function Private Function ConvertDigit(ByVal MyDigit) Select Case Val(MyDigit) Case 1: ConvertDigit = "One" Case 2: ConvertDigit = "Two" Case 3: ConvertDigit = "Three" Case 4: ConvertDigit = "Four" Case 5: ConvertDigit = "Five" Case 6: ConvertDigit = "Six" Case 7: ConvertDigit = "Seven" Case 8: ConvertDigit = "Eight" Case 9: ConvertDigit = "Nine" Case Else: ConvertDigit = "" End Select End Function |
#5
|
|||
|
|||
For a protected form, you need a form field to enter the numeric amount, and
a Ref field to display the written-out version. When the user types a number in the form field and tabs to the next field, the Ref field will display the written-out equivalent. You don't type in the Ref field, only in the form field. For example, assume the numeric form field is named "Amount" and it has "Calculate on exit" checked (both of these are in the form field's Properties dialog). Then the Ref field should look like this: { Ref Amount \*Dollartext \*Caps } Dollars where the braces are created by pressing Ctrl+F9 (or you could create the field code in the Insert Fields dialog). -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Graham Mayor wrote: On a form field? Suzanne S. Barnhill wrote: The \* DollarText formatting switch (with \* Caps) will give you "One Hundred and 25/100." You will have to add "Dollars," which should come at the end (25 cents = 25/100 dollars). "samandmonty" wrote in message ... Is there a formula or some method to display a dollar amount that is entered in a form field as if it were being written on a bank check. For example: I enter 100.25 and it would display One Hundred Dollars and 25/100. |
#6
|
|||
|
|||
I have cleaned up some spacing issues and added some lines to make the code
apply to any formfield with a numeric entry: Sub FormatCurrency() Dim MyNumber Dim ffName As String If Selection.FormFields.Count = 1 Then ffName = Selection.FormFields(1).Name ElseIf Selection.FormFields.Count = 0 And _ Selection.Bookmarks.Count 0 Then ffName = Selection.Bookmarks(Selection.Bookmarks.Count).Nam e End If MyNumber = ActiveDocument.FormFields(ffName).Result If IsNumeric(MyNumber) Then ActiveDocument.FormFields(ffName).Result = _ ConvertCurrencyToEnglish(ByVal MyNumber) Else End End If End Sub Function ConvertCurrencyToEnglish(ByVal MyNumber) Dim Temp Dim Dollars, Cents Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = "Thousand " Place(3) = "Million " Place(4) = "Billion " Place(5) = "Trillion " MyNumber = Trim(Str(MyNumber)) DecimalPlace = InStr(MyNumber, ".") If DecimalPlace 0 Then Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2) Cents = ConvertTens(Temp) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber "" 'convert last 3 digits to English Dollars Temp = ConvertHundreds(Right(MyNumber, 3)) If Temp "" Then Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) 3 Then 'remove last 3 comverted digits MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop 'clean up dollars Select Case Dollars Case "" Dollars = "" '"No Dollars" Case "One " Dollars = "One Dollar" Case Else Dollars = Dollars & "Dollars" End Select 'clean up cents If Dollars = "" Then Select Case Cents Case "" Cents = "" '" And No Cents" Case "One " Cents = "One Cent" Case Else Cents = Cents & "Cents" End Select Else Select Case Cents Case "" Cents = "" '" And No Cents" Case "One" Cents = " And One Cent" Case "One " Cents = " And One Cent" Case Else Cents = " And " & Cents & "Cents" End Select End If If Dollars = "" And Cents = "" Then ConvertCurrencyToEnglish = "Zero" Else ConvertCurrencyToEnglish = Dollars & Cents End If End Function Private Function ConvertHundreds(ByVal MyNumber) Dim Result As String If Val(MyNumber) = 0 Then Exit Function 'append leading zeros to number MyNumber = Right("000" & MyNumber, 3) 'do we have hundreds place digit to convert? If Left(MyNumber, 1) "0" Then Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred " End If 'do we have tens place digit to convert? If Mid(MyNumber, 2, 1) "0" Then Result = Result & ConvertTens(Mid(MyNumber, 2)) Else 'if not, then convert the ones place digit Result = Result & ConvertDigit(Mid(MyNumber, 3)) End If ConvertHundreds = Trim(Result) & " " End Function Private Function ConvertTens(ByVal MyTens) Dim Result As String 'is value between 10 and 19? If Val(Left(MyTens, 1)) = 1 Then Select Case Val(MyTens) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else Select Case Val(Left(MyTens, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select 'convert ones place digit Result = Result & ConvertDigit(Right(MyTens, 1)) End If ConvertTens = Result End Function Private Function ConvertDigit(ByVal MyDigit) Select Case Val(MyDigit) Case 1: ConvertDigit = "One " Case 2: ConvertDigit = "Two " Case 3: ConvertDigit = "Three " Case 4: ConvertDigit = "Four " Case 5: ConvertDigit = "Five " Case 6: ConvertDigit = "Six " Case 7: ConvertDigit = "Seven " Case 8: ConvertDigit = "Eight " Case 9: ConvertDigit = "Nine " Case Else: ConvertDigit = "" End Select End Function -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. samandmonty wrote: Is there a formula or some method to display a dollar amount that is entered in a form field as if it were being written on a bank check. For example: I enter 100.25 and it would display One Hundred Dollars and 25/100. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numeric to Written amount. | Mailmerge |