Formula/Equation in Word Table 1

[SilentAiche]

Folks,

Is there any way to total a column in a Word 2002 (XP) table where the total will recalc automatically if a figure in the column is changed? The sum function (=sum(above)) that is available apparently is a one-time wonder- even the Help function advises that recalcs have to be performed manually (but it doesn't say how!).

Also, are there any implications if the table is in a document that is saved as a Form?

As they say in the Army,

Tanks!

 

[tf1]

This can only be done by manually updating the result. Just right-click on the total and select Update Field, or press F9.


Regards: tf1

 

[fumei]

Actually, there is a way. The following assumes:

4 rows with a text formfield in each. The first three are named Cell1, Cell2, Cell3. The bottom one is named LastCol.

All cells have properties set as Number. The On Error allows the code to run if the user enters text. Form Fields set as Numbers, set themselves to 0 if text is entered. However, the code is expecting an integer and runs OnExit. The form field value is set to 0 after the OnExit macro runs. This way, the code assumes a 0 - which is, in effect, what text would be from a numeric point of view.

The sub is set as the OnExit macro for each cell. That way the result of the final cell always equals the current value of the sum of the form fields above.

Sub UpdateLastCol()
On Error Resume Next
ActiveDocument.FormFields("LastCol").Result = _
    CInt(ActiveDocument.FormFields("Cell1").Result) + _
    CInt(ActiveDocument.FormFields("Cell2").Result) + _
    CInt(ActiveDocument.FormFields("Cell3").Result)
End Sub

Gerry

 

[SilentAiche]

Thank you both. I have very limited experience with macros, Gerry, but I copied your code into a macro in a test document so that perhaps I could learn. I'm not going to ask anyone to take me through it in baby steps. I'm getting compile errors in the early going, but I'm sure I just don't have something basic set properly (keep reading- you'll understand...)

Two questions, just to get me kickstarted. How do I "name" the cells Cell1, Cell2, etc.? Is that done in the "Bookmark" box in the Text Form Field Options dialogue box? Also, I assume it is in this dialogue box that I set UpdateLastCol to run on exit? Finally, should it be called UpdateLastRow since we are totaling a vertical column?

Thanks again for your help- please forgive my ignorance on the subject matter.

 

[tf1]

The cells of a table are numbered as in Excel: Rows run from A and Columns from 1 to give A1, A2, A3, B1, B2, B3, etc.


Regards: tf1

 

[fumei]

Actually, the cells were not named at all in my post, the FormFields are named. And yes, they are named using the Properties of the FormFields (right click, then Properties).

You can name the last formfield in the column whatever you like. Yoy can name it "bob" if you want. It does not matter. It does not matter what you name the other formfields either, it is simply a good idea to explicitly name things.

So.

1. You have formfields in the cells. Because you are using formfields, and logic between them, the fact that they are in cells in a table is completely irrelevant. The table, in this case is ONLY a visual construct. You could have these formfields scattered anywhere in the document, and the logic - and the result - will be the same. They are simply named bookmark ranges really.

2. The formfields are named - right click them, select Properties, and in the Bookmark field, type in a name.

3. Put the sub routine in a code module. This can be the standard ThisDocument, or create a new one (In the Visual Basic Editor Insert > Module)

4. The sub routine can be named UpdateLastRow if you want - again, it does not matter. You can name it George, just as long as you select George as the macro to run OnExit.

5. You must have the sub written already to be able to select it from the list of OnExit macros. These are, as you figured out, selected from right clicking the formfield, and selecting from the dropdown list of OnExit macros.

6. I had it that ALL of the formfields run the OnExit macro. That way, the final formfield always updates everything. You could do it that only the last one (before the total) runs the update routine. Frankly though, you may as well make it run from all of them.

7. Lastly, and most important. You MUST protect the document for forms so the formfields will function correctly. Tools > Protect Document, then select Forms. If you have multiple sections, make sure the sections with the formfields is protected. if you want to be able to edit in other places, make sure those places are NOT protected. use Continuous Section breaks to separate the areas that need protecting, from those that do not need protecting.

As a final note. you can make the shaded aspect of formfields go away, so that they results, the contents, look just like any other text around them. Use View > Toolbars > Forms to display the Forms toolbar. There is a button (usually the third from the right) for Form Field Shading. It is a toggle. Toggled to OFF, the contents of the formfields look like any other text. You can apply Character Styles to them, or format them as muchas you like etc etc.

Gerry

 

[SilentAiche]

Gerry:

I appreciate all that you put into the previous post, and I regret being so long in replying (for some reason, I don't think I was notified of it).

To my amazement, I understood enough of your code example to get the following to work:

Sub UpdateLastCol()
On Error Resume Next
ActiveDocument.FormFields("LastCol1").Result = _
    CInt(ActiveDocument.FormFields("b1").Result) + _
    CInt(ActiveDocument.FormFields("b2").Result) + _
    CInt(ActiveDocument.FormFields("b3").Result)
ActiveDocument.FormFields("LastCol2").Result = _
    CInt(ActiveDocument.FormFields("c1").Result) + _
    CInt(ActiveDocument.FormFields("c2").Result) + _
    CInt(ActiveDocument.FormFields("c3").Result)
ActiveDocument.FormFields("LastCol3").Result = _
    CInt(ActiveDocument.FormFields("d1").Result) + _
    CInt(ActiveDocument.FormFields("d2").Result) + _
    CInt(ActiveDocument.FormFields("d3").Result) + _
    CInt(ActiveDocument.FormFields("d4").Result) + _
    CInt(ActiveDocument.FormFields("d5").Result) + _
    CInt(ActiveDocument.FormFields("d6").Result)
ActiveDocument.FormFields("LastCol4").Result = _
    CInt(ActiveDocument.FormFields("e1").Result) + _
    CInt(ActiveDocument.FormFields("e2").Result) + _
    CInt(ActiveDocument.FormFields("e3").Result) + _
    CInt(ActiveDocument.FormFields("e4").Result) + _
    CInt(ActiveDocument.FormFields("e5").Result) + _
    CInt(ActiveDocument.FormFields("e6").Result)

End Sub

Columns "b" and "c", as well as their totals (LastCol1 and LastCol2) are on Page 4 of my document. Columns "d" and "e" and LastCol3 and LastCol4 are on Page 6.

The problem: when I enter data in the formfields in the table on Page 6, after I exit each formfield the document jumps briefly to the table on Page 4 and then returns - whether I entered new data or was just tabbing through. This "jerky" motion is very distracting.

Also, oddly, in the second table the totals update as soon as I exit any formfield in the table. In the first table, both totals update only after I exit the first total (LastCol1).

Should I have separate macros/modules for each table? Also, do you detect major syntax errors in the code above (for example, should there be a blank line before each section)?

As always, the help is much appreciated!
Tim

 

[KenWright]

Star from me Gerry - I know zip about Word, but I still like that

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[fumei]

OK.....again, and this is a subject that will never go away, no matter how many posts there are in the owrld.

This is a communication and DESIGN issue. The code I posted was built around ONE table. This was because the original post onoy mentioned ONE table.

Clearly, if you have multiple tables of course things will get jumpy, because you are doing calculation and valifations across multiple areas. So.

Yes, I would have a macro for each table. This keeps it cleaner and object specific. You may be able to write a Function that deals with the general donkey work, and pass in arguments (i.e. the number of rows specific to the table), and let the function calculate.

Gerry