Macros to add a row in a word table

[Craig]

I have created a table in word full of FORMTEXT FIELDS. One of the columns
(F) includes a calculation field to deduct column D from column B. I have
then used an template add row macro (see below) which when in the last cell
will copy the previous row. However because of this the forumla in column F
only calculates the same as the first row. I want the fromula it to repeat
for the next row

i.e. F2 = B2-D2
then F3 = B3-D3 my macro currently does F3 = B2-D2 etc.

Add row macro used:
Sub AddRow()
'Run on exit from the last form field in
'the last row of the table
Dim oTable As Table
Dim oRng As Range
Dim oCell As Range
Dim oLastCell As Range
Dim sResult As String
Dim iRow As Integer
Dim iCol As Integer
Dim CurRow As Integer
Dim i As Long

Dim sPassword As String

sPassword = "" 'password to protect/unprotect form
With ActiveDocument
.Unprotect Password:=sPassword 'Unprotect document
Set oTable = .Tables(4) 'Select the appropriate table
iRow = oTable.Rows.Count 'Record the last row number
iCol = oTable.Columns.Count 'Record the last column number
Set oLastCell = oTable.Cell(iRow, iCol).Range 'Record the last cell
sResult = oLastCell.FormFields(1).Result 'Get the value in the last cell
Set oRng = oTable.Rows(iRow).Range 'Add the last row to a range
oRng.Copy 'Copy the row
oRng.Collapse wdCollapseEnd 'collapse the range to its end.
oRng.Select 'the end of the table
Selection.Paste 'Paste the row at the end of the table
CurRow = iRow + 1 'Record the new last row
For i = 1 To iCol 'Repeat for each column
Set oCell = oTable.Cell(CurRow, i).Range 'process each cell in the
row
oCell.FormFields(1).Select 'Select the first field in the cell
With Dialogs(wdDialogFormFieldOptions) 'and name it
.Name = "Col" & i & "Row" & CurRow 'eg Col1Row2
.Execute 'apply the changes
End With
Next i
'Select the formfield in the last cell of the previous row
oLastCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Exit = "" 'and remove the exit macro
.Execute 'apply the changes but note that this clears the value
from the cell
End With
oLastCell.FormFields(1).Result = sResult 'so restore the result of the
cell
.Protect NoReset:=True, Password:=sPassword, _
Type:=wdAllowOnlyFormFields 'Reprotect the form
.FormFields("Col1Row" & CurRow).Select 'and select the next field to be
completed
End With
End Sub

 

[Graham Mayor]

See the example code at http://www.gmayor.com/word_vba_examples.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Craig]

Thanks Graham,

Is there a way to get the formatting to stay the same for the additional row.

I.e if you want column 4 to be limited to 2 characters or if you want
columns 2 and 5 to be in a number or percentage format rather than text.

Thanks Again

 

[Graham Mayor]

The code on that page copies the row and the form fields it contains
exactly.

The "An alternative method of adding a row to a protected table" item (on
which you appear to have based your example) demonstrates how to edit
individual fields in the added row. In the example the last field is edited
to remove the macro and all the fields are edited to provide new names.

You can edit any of the fields and format them as you wish. The arguments
used by the Dialogs() command are listed at the start of the section. The
cells are addressed by

Set oCell = oTable.Cell(CurRow, i).Range

where CurRow is the Row and i the column.

See also both the previous code example and "Repeat a block of formatted
text and form fields based upon the content of another form field" which use
different but complementary methods of writing form fields.
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Craig]

Hi Graham

Sorry I'm very much a beginner at this, From your web I can't see how to
link to two add row macro's

In using the "alternative add row to protected form" macro i also want to
include a repeated calculation element to column 6 I tried inserting this but
it breaks the code

If i = 6 Then 'add a calculation to add the field results in cols 1 to 6
.TextInput.EditType Type:=wdCalculationText, _ (causing
error re text field i think)
Default:="=Col2Row" & CurRow _
& " - Col4Row" & CurRow, _
Format:=""

anny suggestions

 

[Doug Robbins - Word MVP]

Beginner or not, you need to pay more attention to the code example to which
Graham referred you

If i = 6 Then 'add a calculation to add the field results in
cols 1 to 6
.Enabled = False 'Omitting this causes the error
.TextInput.EditType Type:=wdCalculationText, _
Default:="=Col2Row" & CurRow _
& " + Col4Row" & CurRow _
Format:=""
End If


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Graham Mayor]

I believe the following will achieve what you require (a calculation in
column 6 that will add the contents of columns 1 to 5 in that row?) To that
end I have added a formula field to cell 6 and a calculate on exit check to
cell 5. Actually if you had the formula field already in cell 6 of the
previous row that you are duplicating and the check already in cell 5 of
that row then the extra code should be superfluous.

For i = 1 To iCol 'Repeat for each column
Set oCell = oTable.Cell(CurRow, i).Range 'process each cell in the
row
oCell.FormFields(1).Select 'Select the first field in the cell
With Dialogs(wdDialogFormFieldOptions) 'and name it
.name = "Col" & i & "Row" & CurRow 'eg Col1Row2
If i = 5 Then .Calculate = True
.Execute 'apply the changes
End With
If i = 6 Then
For j = oCell.FormFields.Count To 1 Step -1
oCell.FormFields(j).Delete
Next j
oCell.End = oCell.End - 1
oCell.Select
Selection.InsertFormula Formula:="=Sum(left)", NumberFormat:=""
End If
Next i


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Craig]

Thanks for your help,

the code breaks on the following line when wanting to add a row.

oCell.FormFields(1).Select 'Select the first field in the cell

The other problem i incurred is that when changing the formula from
sum(left) to
"=Col2Row" & CurRow _
& " - Col4Row" & CurRow

the curent row did not update but remained the same is this because it no
longer in form field format.

Thanks

 

[Graham Mayor]

Why did you change Sum(Left)? This adds the contents of the fields to the
left (which is what you asked) and is easy to duplicate.
The code I posted in answer to your query removes the form field from the
cell in which the Word formula field is placed. If the form field is no
longer present the macro cannot be used to select it. I am no longer clear
exactly what it is you are trying to achieve.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Craig]

Column A is formtext fields
Column B is form text field for numeric value
Column C emply cells
Column D is form text field for numeric value
Column E is empty cells
Column F calculates the value in B less the value in D i.e F2 = B2-D2, F3 =
B3-D2
Column G calulates the F as a % of D i.e. G2 = D2/F2
Column H is form text with a number.

I don't mind if columns F anf G are form fields as long as they are disabled
for filling in.

I was trying to set up a add row macro that will copy the previous row and
replicate it below. but in doing this i wanted the formulas in columns F and
G to work on there respective rows. unfortunately my best efforts only manage
to copy the formlua and not update it.

 

[Graham Mayor]

OK - can you confirm Column F should that be F3 = B3-D2 or B3-D3?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Craig]

Sorry typo s/be F3 = B3-D3
thanks

OK - can you confirm Column F should that be F3 = B3-D2 or B3-D3?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

Subject to what I think was probably a typo in Column F the following should
work. If Column F was not a typo then you will need to change sCalc for i =
6 to produce the correct calculation.

Sub AddARow()
'Run on exit from the last form field in
'the last row of the table
Dim oTable As Table
Dim oRng As Range
Dim oCell As Range
Dim oLastCell As Range
Dim sResult As String
Dim iRow As Integer
Dim iCol As Integer
Dim sCalc As String
Dim CurRow As Integer
Dim i As Long
With ActiveDocument
.Unprotect Password:="" 'Unprotect document
Set oTable = .Tables(1) 'Select the appropriate table
iRow = oTable.Rows.Count 'Record the last row number
iCol = oTable.Columns.Count 'Record the last column number
Set oLastCell = oTable.Cell(iRow, iCol).Range 'Record the last cell
sResult = oLastCell.FormFields(1).Result 'Get the value in the last cell
Set oRng = oTable.Rows(iRow).Range 'Add the last row to a range
oRng.Copy 'Copy the row
oRng.Collapse wdCollapseEnd 'collapse the range to its end.
oRng.Select 'the end of the table
Selection.Paste 'Paste the row at the end of the table
CurRow = iRow + 1 'Record the new last row
For i = 1 To iCol 'Repeat for each column
Set oCell = oTable.Cell(CurRow, i).Range 'process each cell in the
row
oCell.FormFields(1).Select 'Select the first field in the cell
With Dialogs(wdDialogFormFieldOptions) 'and name it
.name = "Col" & i & "Row" & CurRow 'eg Col1Row2
If i = 6 Then
sCalc = "=Col" & 2 & "Row" & CurRow & _
" - Col" & 4 & "Row" & CurRow
.textdefault = sCalc
End If
If i = 7 Then
sCalc = "=Col" & 4 & "Row" & CurRow & _
" / Col" & 6 & "Row" & CurRow
.textdefault = sCalc
End If
.Execute 'apply the changes
End With
Next i
'Select the formfield in the last cell of the previous row
oLastCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Exit = "" 'and remove the exit macro
.Execute 'apply the changes
'but note that this clears the value from the cell
End With
oLastCell.FormFields(1).Result = sResult 'so restore the result of the
cell
.Protect NoReset:=True, Password:="", _
Type:=wdAllowOnlyFormFields 'Reprotect the form
.FormFields("Col1Row" & CurRow).Select 'and select the next field to be
completed
End With
End Sub


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

OK then the macro I just posted will work. Set the field types in F and G to
be calculation form fields Calculation fields do not have user access so no
need to do anything further with those. The macro will add the correct
calculations

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Craig]

Thats works perfectly thank you

Whan i copy the macro for a different table i occassionally get the code
breaking on the Excute part is there a reason why this might happen, it
doesn't do it all the time

Many thanks once again

 

[Graham Mayor]

Hard to say without testing it with your document. Do however ensure that
the fields already in the table row that you are duplicating are what the
macro expects to find. Which of the execute statements causes the error?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>