Personal website of Gregory K. Maxey, Commander USN (Retired)
The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.
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.
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.
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.