CODEWITHSUNDEEP
vbams-word
pdangelo4
pdangelo4

Reputation: 230

Auto sum table in Word

I am trying to create a 3x6 table in Word 2010 with a simple =SUM(ABOVE)formula in the bottom cells. My problem is having the sum auto adjust/calculate when a user changes a value in one of the above cells.

Does anyone know how to create a macros for this using VBA?

Thanks

Upvotes: 0

Views: 1683

Answers (2)

pdangelo4
pdangelo4

Reputation: 230

So I figured it out. By going into developer mode, you can add text boxes to the document and then use macros to link the text boxes and create an auto sum feature.

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

Unlike Excel formulas, Word fields do not update automatically and there isn't a setting to make them do so. A VBA solution that intercepts any change in the document isn't viable because it would trigger code for each keystroke.

There are practical solutions, in order of preference:

Option 1 - Update the document manually:

Ctrl+A (select All) Then press F9 (update all document fields).

You can also do the update selectively on the fields you want. For example, select the table's last row with the mouse then press F9

Option 2 - Embed an Excel Table and use formulas for the last row. Excel will automatically update the formula when the table is edited.

Option 3 - Intercept the change the application's WindowSelectionChange event

' Code module ThisDocument
Option Explicit
Private WithEvents app As Word.Application
Private Sub app_WindowSelectionChange(ByVal Sel As Selection)
    If Sel.Range.InRange(Tables(1).Range) Then Fields.Update ' or: Tables(1).Fields.Update
End Sub
Private Sub Document_Open()
    Set app = Me.Application
End Sub

Option 4- Create an automatic updater with VBA, a function that invokes itself regularly every second or so and update all (or selective if you want) fields.

' Code module ThisDocument
 Option Explicit
 Public Sub updateFields()
    Fields.Update ' or: Tables(1).Fields.update
    Application.OnTime Now + TimeSerial(0, 0, 1), "ThisDocument.updateFields"
End Sub
Private Sub Document_Open()
    Application.OnTime Now + TimeSerial(0, 0, 1), "ThisDocument.updateFields"
End Sub

Upvotes: 0

Related Questions