Resting Anchor

The Anchorage

Personal website of Gregory K. Maxey, Commander USN (Retired)

Currency Format
(A Microsoft Word Help & Tip page by Gregory K. Maxey)

DISCLAIMER/TERMS OF USE

The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.

If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!


This Microsoft Word Tips & Microsoft Word Help page was almost chopped when I converted the site. However, as many people ask, "How can I enter numbers in a table cell and have currency formatting automatically applied?" I am going to post it anyway. The proposed solution isn't very practical simply because an embedded Excel spread sheet or a protected form with fields is a more practical approach. Regardless, the method proposed will give you a taste of using VBA to evaluate conditions, format text, and maneuver around in a document. As an added bonus you will also get a taste of Word formula fields.

As an example, let take a small Word table that lists the imaginary quarterly sales by region of a small company.

format currency 1

Here we have an 5X8 table (5 columns and 8 rows). Tables are characterized by columns designated A, B, C, ... from right to left and rows designated 1, 2, 3 ... from top to bottom. In our example, the second quarter sales for the Western region is found in cell B5, total third quarter sales is found in D8, etc. The total sales for each quarter is calculated using a Word formula field. The graphic shows the field code.

format currency 2

The field used is a formula field with a specific formatting picture switch. For more on field codes and their construction, see: Word Fields.

As you can see from the table above, the formatting of the totals is provided by the \# formatting switch in the field code. We are looking for a method of automatically applying a similar format to the values entered for each region in the quarterly columns. Here's is the catch. Word won't do this automatically in a simple Word form.

You have to do something after you enter the last digit in the cell. Firing a macro is the something that you can do. The macro that we are going to use in this example will do several things. First we will test to see if we are actually in a table cell, then determine if the information in the cell is in a numeric form that can take a currency format, then apply currency format, then advance the cursor to the next data cell, and finally update the formula fields in the table.

VBA Script:
Sub ConvertToCurrencyAndAdvance()
Dim i As Long
Dim j As Long
Dim oNum As Range
  If Not Selection.Information(wdWithInTable) Then Exit Sub
  i = Selection.Information(wdStartOfRangeRowNumber)
  j = Selection.Information(wdStartOfRangeColumnNumber)
  With Selection.Tables(1)
    Set oNum = .Cell(i, j).Range
    oNum.End = oNum.End - 1
    If IsNumeric(oNum) Then
      .Cell(i, j).Range = FormatCurrency(Expression:=oNum, _
         NumDigitsAfterDecimal:=2, IncludeLeadingDigit:=vbTrue, _
         UseParensForNegativeNumbers:=vbTrue)
      If i < .Rows.Count - 1 Then
        .Cell(i + 1, j).Select
      ElseIf j < .Columns.Count Then
        .Cell(2, j + 1).Select
      Else
        .Cell(2, 2).Select
      End If
      Selection.Collapse Direction:=wdCollapseStart
    Else
      Beep
      oNum.Select
    End If
    .Range.Fields.Update
  End With
lbl_Exit:
Exit Sub
End Sub

All you have to do is assign the above macro to a simple keyboard shortcut (e.g., ALT+down arrow). Enter your data and fire the macro with the shortcut.

Site Note icon See: Installing Macros for instructions on how to set up and use the macros provided in this Microsoft Word Help & Microsoft Word Tips page.

Of course the macro provided above is customized for the sample table or any table where the first row and first column is used for labels and the last row is used for calculations. The repositioning of the cursor is top to bottom/right to left. This could easily be changed to right to left/top to bottom.

If you don't want to monkey around with the the keyboard shortcut and you can wait until you have entered all the data to complete the formatting, then you can use this macro to apply currency format to qualifying selected cells:

VBA Script:
Sub ConvertSelectedNumbersInTableToCurrencyFormat()
Dim oCl As Word.Cell
Dim oRng As Range
  If Selection.Type = wdSelectionIP Or _
      Not Selection.Information(wdWithInTable) Then
    MsgBox "Select a cell or range of cells before running" _
      & " this macro.", , "Nothing Selected"
    Exit Sub
  End If
  For Each oCl In Selection.Cells
    Set oRng = oCl.Range
    'Drop of the end of cell mark
    oRng.End = oRng.End - 1
    With oRng
      If IsNumeric(oRng) Then
        .Text = FormatCurrency(Expression:=.Text, NumDigitsAfterDecimal:=2, _
                IncludeLeadingDigit:=vbTrue, UseParensForNegativeNumbers:=vbTrue)
      End If
      If oRng.Characters.Count = 1 Then GoTo Skip
      On Error GoTo Skip
      'Catch errors here
      If InStr(oRng.Text, "$") = False Then
        oRng.Font.Color = wdColorRed
        oRng.Select
        MsgBox "Cell content is not numerical.", , "Error"
        Selection.Collapse wdCollapseEnd
      End If
Skip:
    End With
  Next oCl
lbl_Exit
Exit Sub

That's it! I hope you have found this tips page useful and informative.

Share Stumbleupon

PAYMENTS/DONATIONS

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

Do you want to make a payment for consulting work or donate to help support this site?

PayPal is a safe, easy way to pay online.

Use the appropriate currency "Donate" button to make a payment or donation.


Search my site or the web using Google Search Engine

Google Search Logo

Or

JustAnswerAsk a Word Expert OnlineSubmit