Add new row in Table + new set of Form Fields

[CokeSarkier]

Hi,
This is my first messgae on this board, and I guess I'm straight in with a
common Q (can't find previous Qs, tho)...

I have a table with text form fields in each of the 5 cells in row 2 (row 1
has the 5 headings). Is there a way of assigning an 'on exit' macro to the
last cell of the last row that a) checks for TAB being pressed (indicating a
new row, as is normal in Word) and then b) adds new text form fields in the
new row...ready for the same scenario to happen again (i.e. Potentially press
TAB at the end of that row, too)?

Hope all this makes sense, and that someone can help me out, too! Thanks,
in advance.

 

[Stephanie Krieger]

Hi,

Sure -- you can use an On Exit macro for something much
like this. It won't read your tab character, but you can
use a message box --

Create a document with the 2 rows you mentioned (5
columns, 2 rows, 2nd row has text fields) and use the
following macro as the On Exit macro from the last (5th)
text field in the row).

The code below asks the user if they want a new row, and
if they say yes -- it will insert the row, and add the
text fields -- naming the text fields with the new row's
number for a consistent naming convention -- and placing
the same macro as the exit macro for the 5th text field
in the new row. If you have a password on your document
protection -- be sure to add it in the
ActiveDocument.Protect line of code near the end.

Note that you can't insert a row while the document is
protected, which is the reason for the unprotect\protect
lines of code. Also -- the NoReset = True property
setting is essential -- without that, all completed
content of all fields in the document will be reset when
the document is protected at the end of the macro. (If
you use option explicit, be sure to declare the variables
I use in the macro below.)

Here's the code:

Sub addrow()


response = MsgBox("Add new row?", vbQuestion + vbYesNo)
If response = vbYes Then
ActiveDocument.Unprotect

Selection.InsertRowsBelow 1
Selection.Collapse (wdCollapseStart)
myRow = Selection.Information(wdStartOfRangeRowNumber)

Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text1row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text2row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text3row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text4row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text5row" & myRow
.Enabled = True
.ExitMacro = "addrow"
End With
myNewField = "text1row" & myRow
ActiveDocument.Protect NoReset:=True,
Type:=wdAllowOnlyFormFields
ActiveDocument.Range.FormFields(myNewField).Select
End If

End Sub
______________________

Hope that's helpful!

Best,
Stephanie Krieger
author of Microsoft Office Document Designer (from
Microsoft Learning)
email: MODD_2003 at msn dot com
blog: arouet.net

 

[CokeSarkier]

Hi, Stephanie

You help has been great, so far. Code works perfectly. However...I tried
to work on the ELSE bit of the IF - If No is selected (no more rows) can the
next field, just below the table, be the next active form field? Basically,
the table is of a dynamic row length, but there is still more of the form
left to fill in.

Thanks, in advance Stephanie.

CokeSarkier

 

[Stepahnie Krieger]

Hi,

Sure -- that's not a problem. See the last line of code
before the End If line in the macro I posted for you
yesterday. It selects the form field at the beginning of
the new row.

ActiveDocument.Range.FormFields(myNewField).Select

Under Else:, just copy that line of code and replace
myNewField with the name of the form field you want
selected (in quotes). myNewField isn't in quotes because
it's a variable. (To name a formfield, right-click on it
and select properties. Type the desired name in the text
box labeled bookmark.)

This is probably my last visit to the newsgroups this
week. Hope that was helpful.

Best,
Stephanie

 

[CokeSarkier]

Hi, Stephanie

Thanks for your help - it's all working, beautifully (...so far!!). The
only thing I had to do (apart from your advice on the Else bit) was to take
the number of remaining text fields away from the formfield count (in this
case, just 1) - it had problems naming the new form fields, and kept renaming
my remaining the final form field that was outside the table.

Superb help. I hope everyone's like you!!

 

[richardall]

I followed the recommendations listed above but didn't have the same
success. I think my problem is that I have 3 tables in my document and
they are different column count.

Could this script be written to look to see what the current row is and
duplicate it no matter how many columns it has and copy in the same field
types?

This would make it extremely flexible and usable in many uses.

Greatest regards,
Richard

 

[Doug Robbins]

If there is more than one table in the document, change
ActiveDocument.Tables(1) to Selection.Tables(1) in the following code

' Macro created 02/02/03 by Doug Robbins

' To add a new row to a table containing formfields in every column

' automatically on exit from the last cell in the present last row of the
table

Dim rownum As Integer, i As Integer

ActiveDocument.Unprotect

ActiveDocument.Tables(1).Rows.Add

rownum = ActiveDocument.Tables(1).Rows.Count

For i = 1 To ActiveDocument.Tables(1).Columns.Count

ActiveDocument.FormFields.Add
Range:=ActiveDocument.Tables(1).Cell(rownum, i).Range,
Type:=wdFieldFormTextInput

Next i

ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
ActiveDocument.Tables(1).Columns.Count).Range.FormFields(1).ExitMacro =
"addrow"

ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
1).Range.FormFields(1).Select

ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True


--
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

 

[richardall]

Very nice!

In seeing the new rows, I see that I didn't fully explain the situation.
My apologies.

I have three tables in the document with a different number of columns.
In one of the tables, the field in the first column is a pull down and the
remaining fields are text fields.

Is there a way to duplicate the former row in this situation?

Just to ask, could this duplication also include if a cell in the table
had a check box field and a text entry filed. Duplicate them both?

Thank you for the help.

 

[Doug Robbins]

You would need to modify the following section of the macro, so that
wdFieldFormTextInput fields were only added to the cells in which you wanted
textbox formfields and the other types (wdFieldFormCheckBox or
wdFieldFormDropDown) of fields were inserted into the cells in which you
want them.
For i = 1 To ActiveDocument.Tables(1).Columns.Count

ActiveDocument.FormFields.Add
Range:=ActiveDocument.Tables(1).Cell(rownum, i).Range,
Type:=wdFieldFormTextInput

Next i

Of course, you will also need to come up with the code the add the items to
the pull down formfield. Here is the example from the DropDown Object in
the Visual Basic Help File for how to do that:

Set ffield = ActiveDocument.FormFields(1).DropDown
If ffield.Valid = True Then
ffield.ListEntries.Add Name:="Hello"
Else
MsgBox "First field is not a drop down"
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

 

[shanbones]

I have attempted to use this macro but I am getting an error message when I
copy/paste it. The following areas turn red:

Range:=ActiveDocument.Tables(1).Cell(rownum, i).Range,
Type:=wdFieldFormTextInput

Next i

ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
ActiveDocument.Tables(1).Columns.Count).Range.FormFields(1).ExitMacro =
"addrow"

ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
1).Range.FormFields(1).Select


I thought, at first that maybe I had a line wrapping problem, but I am also
getting a "Compile Error: Expected: expression" for the following area:

Range:=

TYIA for your assistance,
shanbones

 

[Doug Robbins]

The mail program has inserted a line break. In the VBE, place the cursor
before Type and press the backspace key until the two lines of code become
one, or insert a VBA line break by inserting a space then and underscore
character after the , at the end of the first line

Range:=ActiveDocument.Tables(1).Cell(rownum, i).Range, _
Type:=wdFieldFormTextInput

--
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

 

[shanbones]

Doug,

Thank you. That took care of the line spacing issues. However, I still
receive an error message ("Compile Error: Expected: expression") highlighting
the := after "Range." Please advise.

Thank you,
shanbones

 

[shanbones]

One more thing...I also get a "Can't execute code in break mode" message when
I try to run the macro when the form is protected.

 

[DuncanG]

Please help with the add list entries for a dropdown
I can't quite figure out at what point this should be added

Thanks
Duncan

 

[DuncanG]

Please ignore my previous question, I figured it out.

Thanks
Duncan

 

[DuncanG]

I have a protected form with a table which adds a new row containing form
fields if the user requres as shown in this discussion. How can the user
delete a row if a mistake is made?

Thanks
Duncan

 

[JohnLute]

Hi, Stephanie.

I can't believe I found such an informative thread on my first search! I
have a couple questions as I'm entirely unfamiliar with Word's macro
functions (I'm more familiar with Access and I suppose there are some
similarities).

1. My table has 10 columns. Will this code still work?
2. How do I create this code in Word? I'm assuming it's Tools > Macro > VBE.

Thanks so much!

 

[Jay Freedman]

Hi John,

It's easier to answer question 2 first -- yes, use Tools > Macro > VBE (or
the shortcut Alt+F11). You'll also need to create a module to hold the code.
Full instructions are at http://www.gmayor.com/installing_macro.htm.

For question 1: No, Stephanie's code inserts only five form fields in each
new row. You could add another five segments of code to fill the remaining
five columns, but that would be tedious, not to mention making the macro
that much harder to update if your requirements change in the future. I
recommend the following version, which figures out how many times to repeat
the action by asking how many columns the table has. [Note that this version
will generate an error if the table contains any merged or split cells. I
assume it's all uniform.]

Sub addrow()
Dim response As Integer
Dim myRow As Long
Dim myCount As Long
Dim colCount As Long
Dim colIndex As Long
Dim myNewField As String

response = MsgBox("Add new row?", vbQuestion + vbYesNo)
If response = vbYes Then
ActiveDocument.Unprotect

colCount = Selection.Tables(1).Columns.Count

Selection.InsertRowsBelow 1
Selection.Collapse (wdCollapseStart)
myRow = Selection.Information(wdStartOfRangeRowNumber)

For colIndex = 1 To colCount - 1
Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text" & colIndex & "row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Next colIndex

Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text" & colCount & "row" & myRow
.Enabled = True
.ExitMacro = "addrow"
End With
myNewField = "text1row" & myRow
ActiveDocument.Protect NoReset:=True, _
Type:=wdAllowOnlyFormFields
ActiveDocument.Range.FormFields(myNewField).Select
End If

End Sub

A couple of other comments:

- You may decide that the prompt "Add new row?" is unnecessary. If so,
remove the MsgBox line, the If statement that follows it, and the End If at
the end of the macro.

- The Dim statements at the start of the code are "optional" but highly
recommended; see http://word.mvps.org/FAQs/MacrosVBA/DeclareVariables.htm.

- For a macro that might be used in other templates, there should be some
error-trapping. At a minimum, the macro should start by making sure that the
Selection is currently in a table, and not in a form field elsewhere in the
document (it shouldn't be, since this is an exit macro, but you never know
whether the next template obeys the unwritten rules). The statements

If Not Selection.Information(wdWithInTable) Then
Exit Sub
End If

when placed right after the Dim statements will prevent such errors.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[JohnLute]

Thanks, Jay!

I've created a Macro named "AddRow" and included the code below however,
when I exit the field in the last cell of the row the macro doesn't execute.

Again, I'm rather new to the world of Word macros and I can't see where
I've gone wrong. Any suggestions you might have would be greatly appreciated.

Thanks!

--
www.Marzetti.com

Hi John,

It's easier to answer question 2 first -- yes, use Tools > Macro > VBE (or
the shortcut Alt+F11). You'll also need to create a module to hold the code.
Full instructions are at http://www.gmayor.com/installing_macro.htm.

For question 1: No, Stephanie's code inserts only five form fields in each
new row. You could add another five segments of code to fill the remaining
five columns, but that would be tedious, not to mention making the macro
that much harder to update if your requirements change in the future. I
recommend the following version, which figures out how many times to repeat
the action by asking how many columns the table has. [Note that this version
will generate an error if the table contains any merged or split cells. I
assume it's all uniform.]

Sub addrow()
Dim response As Integer
Dim myRow As Long
Dim myCount As Long
Dim colCount As Long
Dim colIndex As Long
Dim myNewField As String

response = MsgBox("Add new row?", vbQuestion + vbYesNo)
If response = vbYes Then
ActiveDocument.Unprotect

colCount = Selection.Tables(1).Columns.Count

Selection.InsertRowsBelow 1
Selection.Collapse (wdCollapseStart)
myRow = Selection.Information(wdStartOfRangeRowNumber)

For colIndex = 1 To colCount - 1
Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text" & colIndex & "row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Next colIndex

Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text" & colCount & "row" & myRow
.Enabled = True
.ExitMacro = "addrow"
End With
myNewField = "text1row" & myRow
ActiveDocument.Protect NoReset:=True, _
Type:=wdAllowOnlyFormFields
ActiveDocument.Range.FormFields(myNewField).Select
End If

End Sub

A couple of other comments:

- You may decide that the prompt "Add new row?" is unnecessary. If so,
remove the MsgBox line, the If statement that follows it, and the End If at
the end of the macro.

- The Dim statements at the start of the code are "optional" but highly
recommended; see http://word.mvps.org/FAQs/MacrosVBA/DeclareVariables.htm.

- For a macro that might be used in other templates, there should be some
error-trapping. At a minimum, the macro should start by making sure that the
Selection is currently in a table, and not in a form field elsewhere in the
document (it shouldn't be, since this is an exit macro, but you never know
whether the next template obeys the unwritten rules). The statements

If Not Selection.Information(wdWithInTable) Then
Exit Sub
End If

when placed right after the Dim statements will prevent such errors.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

Hi, Stephanie.

I can't believe I found such an informative thread on my first
search! I have a couple questions as I'm entirely unfamiliar with
Word's macro functions (I'm more familiar with Access and I suppose
there are some similarities).

1. My table has 10 columns. Will this code still work?
2. How do I create this code in Word? I'm assuming it's Tools > Macro

Thanks so much!

 

[Jay Freedman]

Hi John,

You have to "kickstart" the process:

- Create the table in the template. Include any header rows and then one
regular row.

- Insert a form field in each cell of that row.

- Open the Properties dialog of the form field in the last column (most
easily by double-clicking the field).

- Pull down the "Exit" dropdown in the dialog and choose AddRow as the exit
macro. Then click OK.

- Protect the template for forms. Save and close it.

- Use File > New and select the template to create a new document based on
the template.

When you exit that last form field in the existing row, the macro will run.
It will create a new row, fill it with form fields, and set the Exit macro
of the last new field to the same macro.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

Thanks, Jay!

I've created a Macro named "AddRow" and included the code below
however, when I exit the field in the last cell of the row the macro
doesn't execute.

Again, I'm rather new to the world of Word macros and I can't see
where I've gone wrong. Any suggestions you might have would be
greatly appreciated.

Thanks!

Hi John,

It's easier to answer question 2 first -- yes, use Tools > Macro >
VBE (or the shortcut Alt+F11). You'll also need to create a module
to hold the code. Full instructions are at
http://www.gmayor.com/installing_macro.htm.

For question 1: No, Stephanie's code inserts only five form fields
in each new row. You could add another five segments of code to fill
the remaining five columns, but that would be tedious, not to
mention making the macro that much harder to update if your
requirements change in the future. I recommend the following
version, which figures out how many times to repeat the action by
asking how many columns the table has. [Note that this version will
generate an error if the table contains any merged or split cells. I
assume it's all uniform.]

Sub addrow()
Dim response As Integer
Dim myRow As Long
Dim myCount As Long
Dim colCount As Long
Dim colIndex As Long
Dim myNewField As String

response = MsgBox("Add new row?", vbQuestion + vbYesNo)
If response = vbYes Then
ActiveDocument.Unprotect

colCount = Selection.Tables(1).Columns.Count

Selection.InsertRowsBelow 1
Selection.Collapse (wdCollapseStart)
myRow = Selection.Information(wdStartOfRangeRowNumber)

For colIndex = 1 To colCount - 1
Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text" & colIndex & "row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Next colIndex

Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text" & colCount & "row" & myRow
.Enabled = True
.ExitMacro = "addrow"
End With
myNewField = "text1row" & myRow
ActiveDocument.Protect NoReset:=True, _
Type:=wdAllowOnlyFormFields
ActiveDocument.Range.FormFields(myNewField).Select
End If

End Sub

A couple of other comments:

- You may decide that the prompt "Add new row?" is unnecessary. If
so, remove the MsgBox line, the If statement that follows it, and
the End If at the end of the macro.

- The Dim statements at the start of the code are "optional" but
highly recommended; see
http://word.mvps.org/FAQs/MacrosVBA/DeclareVariables.htm.

- For a macro that might be used in other templates, there should be
some error-trapping. At a minimum, the macro should start by making
sure that the Selection is currently in a table, and not in a form
field elsewhere in the document (it shouldn't be, since this is an
exit macro, but you never know whether the next template obeys the
unwritten rules). The statements

If Not Selection.Information(wdWithInTable) Then
Exit Sub
End If

when placed right after the Dim statements will prevent such errors.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

Hi, Stephanie.

I can't believe I found such an informative thread on my first
search! I have a couple questions as I'm entirely unfamiliar with
Word's macro functions (I'm more familiar with Access and I suppose
there are some similarities).

1. My table has 10 columns. Will this code still work?
2. How do I create this code in Word? I'm assuming it's Tools >
Macro
VBE.

Thanks so much!