Re: Macros to add a row in a word table
- From: Craig <Craig@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 27 Jul 2009 02:40:01 -0700
Sorry typo s/be F3 = B3-D3
thanks
"Graham Mayor" wrote:
OK - can you confirm Column F should that be F3 = B3-D2 or B3-D3?.
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP
My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Craig wrote:
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" wrote:
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
My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Craig wrote:
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" wrote:
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
My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Craig wrote:
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
"Graham Mayor" wrote:
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
My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Craig wrote:
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" wrote:
See the example code at
http://www.gmayor.com/word_vba_examples.htm
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP
My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Craig wrote:
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
- Follow-Ups:
- Re: Macros to add a row in a word table
- From: Graham Mayor
- Re: Macros to add a row in a word table
- References:
- Macros to add a row in a word table
- From: Craig
- Re: Macros to add a row in a word table
- From: Graham Mayor
- Re: Macros to add a row in a word table
- From: Craig
- Re: Macros to add a row in a word table
- From: Graham Mayor
- Re: Macros to add a row in a word table
- From: Craig
- Re: Macros to add a row in a word table
- From: Graham Mayor
- Re: Macros to add a row in a word table
- From: Craig
- Re: Macros to add a row in a word table
- From: Graham Mayor
- Re: Macros to add a row in a word table
- From: Craig
- Re: Macros to add a row in a word table
- From: Graham Mayor
- Macros to add a row in a word table
- Prev by Date: Re: Macros to add a row in a word table
- Next by Date: Re: Macros to add a row in a word table
- Previous by thread: Re: Macros to add a row in a word table
- Next by thread: Re: Macros to add a row in a word table
- Index(es):
Relevant Pages
|
