The Anchorage

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

• Quarterdeck
• What's New
• Microsoft Word Tips
• Consulting
• Chain Locker
• Lucky Bag
• Contact
• Search

Do you have ad-blocking software enabled? While I respect your right to do so, your donations and the minimal advertisements on this site help to defray internet and other costs of providing this content. Please consider excluding this website from blocking or turning off the blocker while browsing this site.

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.

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.

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.

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.

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:

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.

This page was last updated on March 09, 2021

Website Privacy Statement

Back to top     Back to Word Tips