Remove bulk from macro if possible

[Tonya Marshall]

I've constructed a form that the user can add rows to a table if more
are needed. The macro unlocks the form, goes to a bookmark and adds
another row and then form text fields are added to each of 8 columns,
the form is relocked and the cursor sits at the beginning of the row at
the first text field. I had to use a keyboard macro to add the text
fields and would like to shorten it up to a few lines of code that adds
text fields to the 8 columns and then rests on the right side of the
last text field so that the bookmark can be repositioned to sit in the
last row. Here is the code minus a lot of the text field addition
lines. That area in question is separated by double paragraphs.

Sub AddRow()
Set aDoc = ActiveDocument
If aDoc.ProtectionType <> wdNoProtection Then
aDoc.Unprotect
End If
ActiveDocument.Bookmarks("AddRow").Select
Selection.MoveRight Unit:=wdCell

Selection.FormFields.Add Range:=Selection.Range, Type:= _
wdFieldFormTextInput
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.FormFields.Add Range:=Selection.Range, Type:= _
wdFieldFormTextInput

With ActiveDocument.Bookmarks
.Add Range:=Selection.Range, Name:="AddRow"
.DefaultSorting = wdSortByName
.ShowHidden = False
End With
Selection.SelectRow
Selection.MoveLeft Unit:=wdCharacter, Count:=1
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True
End If
End Sub

Thanks

 

[Jean-Guy Marcil]

Hi Tonya,

Would it be possible to change your approach?
If you want a really short macro (thus easier to maintain and modify...) why
not create an autotext representing a row with all form fields already in
the autotext.

Then, all the macro would do is place the cursor under the table an insert
the autotext, then move up to select the first cell...
Is that possible for your context?

--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Tonya,

Here's a macro that I created for someonelse who wanted to do a similar
thing. This would be set to run on exit from the formfield in the last cell
of the last row of the table. It sets the Run Macro on Exit property of the
last cell in the row that it adds to run the macro so that every time you
exit from the present last row of the table, another row with formfields is
added.

Sub addrow()

' 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


End Sub

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Tonya Marshall]

The macro sounds great but it has a bunch of red lines in it in the VBE.
Perhaps it wrapped wrong? When I exit a row in the locked form it just
goes to the top text field.
Thanks, Doug. I want to keep working on this because I like the idea of
it. This is in my WD2003 right now but will be a group template in WD2002.
Tonya

Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS
wrote:

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Tonya.

The red lines result for word wrapping. There are a total of 9 command
lines in the macro, the beginning of each of which I have identified with
numbers below.

#1 Dim rownum As Integer, i As Integer
#2 ActiveDocument.Unprotect
#3 ActiveDocument.Tables(1).Rows.Add
rownum = ActiveDocument.Tables(1).Rows.Count
#4 For i = 1 To ActiveDocument.Tables(1).Columns.Count
#5 ActiveDocument.FormFields.Add
Range:=ActiveDocument.Tables(1).Cell(rownum, i).Range,
Type:=wdFieldFormTextInput
#6 Next i
#7 ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
ActiveDocument.Tables _(1).Columns.Count).Range.FormFields(1).ExitMacro =
"addrow"
#8 ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
1).Range.FormFields(1).Select
#9 ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Tonya Marshall]

This line comes up with a compile error. The rest of them seem to be OK now.

ActiveDocument.Tables(1).Cell(ActiveDocument.Tables1).Rows.Count,(1).Range.FormFields(1).Select
--
Tonya Marshall
tonz AT harborside DOT com

Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS
wrote:

 

[Jonathan West]

This line comes up with a compile error. The rest of them seem to be OK now.

ActiveDocument.Tables(1).Cell(ActiveDocument.Tables1).Rows.Count,(1).Range.F
ormFields(1).Select

That looks like it should be

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

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

That is what it should be.

The original was

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


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Tonya Marshall]

Oops, a misplaced ) in mine. That fixes the line but now how is it
activated? Tabbing out of the last line just takes it to the top text field.
Tonya

Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS
wrote:

 

[Tonya Marshall]

Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS
wrote:

That is what it should be.

The original was

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

Line #3 gives this message: Error 4120 Bad parameter
ActiveDocument.Tables(1).Rows.Add rownum =
ActiveDocument.Tables(1).Rows.Count

 

[Jean-Guy Marcil]

Hi Tonya,

These are actually 2 lines:

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

Which one gives you the error message?

A quick question:

I understand you have form fields in your table, but do you also have form
fields outside and below the table?
--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Tonya Marshall]

Thanks, Jean-Guy, that fixed that but running the macro with a toolbar
button and then tabbing out of the last column adds 8 fields to one
column instead of one field to 8 columns. Also, how do I make the macro
run automatically when the template is opened rather than clicking a
toolbar button?

This is what Doug wrote, "This would be set to run on exit from the
formfield in the last cell
of the last row of the table. It sets the Run Macro on Exit property of
the
last cell in the row that it adds to run the macro so that every time you
exit from the present last row of the table, another row with formfields is
added."

--
Tonya Marshall
tonz AT harborside DOT com

Hi Tonya,

These are actually 2 lines:

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

Which one gives you the error message?

A quick question:

I understand you have form fields in your table, but do you also have form
fields outside and below the table?
--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Tonya Marshall]

Oops! Forgot to answer this question: No, all of the formfields are in
the table.
Tonya

I understand you have form fields in your table, but do you also have form
fields outside and below the table?

Hi Tonya,

These are actually 2 lines:

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

Which one gives you the error message?

A quick question:


--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Tonya Marshall]

lol ... never mind, I have it working. Still needs a toolbar button to
run it, but it works beautifully. Thanks all

--
Tonya Marshall
tonz AT harborside DOT com

Hi Tonya,

These are actually 2 lines:

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

Which one gives you the error message?

A quick question:

I understand you have form fields in your table, but do you also have form
fields outside and below the table?
--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jean-Guy Marcil]

Hi Tonya,

Personally I would not set the macro to run automatically on exit from the
last cell. Simply because as a user, I know that if I tab out of the last
cell, Word will bring me back to the first cell. This is expected behaviour.
So, I may want to review my entries, tab out of the last cell to go back to
the beginning, and then, paf, a new row! In my example, I did not want that
new row, now I am stuck with it (it is a protected form)...., and as a user
I get very frustrated.

So, personally I would go with the toolbar button...
Also, consider this: If you give your users the chance to add rows.... They
will make mistakes! Adding too many tows... or just plainly changing their
mind... So you may want to add a "Remove row" function...

Just my 2 cents!
--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

Thanks, Jean-Guy, that fixed that but running the macro with a toolbar
button and then tabbing out of the last column adds 8 fields to one
column instead of one field to 8 columns. Also, how do I make the macro
run automatically when the template is opened rather than clicking a
toolbar button?

This is what Doug wrote, "This would be set to run on exit from the
formfield in the last cell
of the last row of the table. It sets the Run Macro on Exit property of
the
last cell in the row that it adds to run the macro so that every time you
exit from the present last row of the table, another row with formfields is
added."

 

[Jean-Guy Marcil]

Oops, another thought,

Make sure that your users understand that this "Add row" function adds a row
at the end of the table, not anywhere they want.

This might be useful to consider though... OTOH, I do not know the
particular of your form, so it may not apply...
If I fill the table then realize that I forgot the third element of a list
of 25 (which should go in row #3)... This is a protected form, so I cannot
just select, copy, paste one row lower...

You could, modify the code so that user can add a row anywhere. They would
indicate the position by placing the cursor in the row above where the new
row should be.

--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

Thanks, Jean-Guy, that fixed that but running the macro with a toolbar
button and then tabbing out of the last column adds 8 fields to one
column instead of one field to 8 columns. Also, how do I make the macro
run automatically when the template is opened rather than clicking a
toolbar button?

This is what Doug wrote, "This would be set to run on exit from the
formfield in the last cell
of the last row of the table. It sets the Run Macro on Exit property of
the
last cell in the row that it adds to run the macro so that every time you
exit from the present last row of the table, another row with formfields is
added."

 

[Tonya Marshall]

I agree ... I ran it with the button, then tabbed out of the row and it
added 8 text fields to one column. I would rather just have the button
add a row without activating the exit feature. What part of the macro do
I need to remove to deactive the on exit?

Since the table is very short (4 rows) they will have a lot of room.
Adding a remove row feature would be very nice and I could make a button
that says, "click in the row you want to remove then click here." I'll
have plenty of room to do that on the toolbar.

Speaking of the toolbar, there are a lot of click-happy people that
close toolbars. I tried making a macro that automatically opened the
toolbar when opening a document from the template but it didn't work
(AutoOpen with unprotect before and protect after)

I truly appreciate all of this help.

 

[Tonya Marshall]

They may only add a row at the bottom. If it's not in a specific order
it doesn't matter. It's a very simple table and I'm trying to make the
process as foolproof and painless as possible. The delete row macro
works fine and if they don't follow the instructions they may delete
their work. They're not entering a large amount of data into each row
and it wouldn't be extremely difficult to do a row over again.
Hopefully they will think before they click.

I would like to remove the automatic part of it as it doesn't work well
and they may end up with a mess as it adds 8 text fields into one
column. The row are sized "at least" so they will expand when data is
entered.

If you would like to see it, I would be happy to email it to you. BTW
spammers can collect your email out of your signature down there.

 

[Jean-Guy Marcil]

Hi Tonya

To give a chance to your users to back out of a mistake, try something like
this:

'_______________________________________
Dim CheckFirst As Long

CheckFirst = MsgBox("Are you sure you want to delete that row?", _
vbExclamation + vbYesNo, "Delete row?")

If CheckFirst = 6 Then
'Click on Yes button returns 6
'Click on No button returns 7
'Put the delete row code here
MsgBox "Delete the row!"
Else
Exit Sub
End If
'_______________________________________

Yeah... I figured that as possible... But my ISP screwed up last year when I
activated my account. They gave me a "Used" e-mail address... The first day
I activated it there were something like 25 unread messages... a lot of them
were subscriptions to on-line bulletins about cooking, fashion,
entertainment.... don't want to stereotype... but chances are it was a
woman! I used to even get bills... I had to fight with the phone company
(who is my ISP provider by the way) to convince them I was not this other
person so they did not have to keep sending me phone bill details!
I used to get over 20 "spam type" messages a day. I managed to unsubscribe
to a lot, ignored the rest... I am now down to 5-10 a day... so even if the
spammers pick it up... the damage has been done!

Thanks for the tip anyway!
--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Tonya Marshall]

Hi Jean-Guy,

That's a nice thing to do, but I get really tired of messages asking me
if I really want to do something. The row they were deleting would not
be apparent so they wouldn't know whether to delete it or not. I expect
they will learn even if it's the hard way. The data entered is small and
having to re-enter it would not be a big problem, just a bit
frustrating. I consider it a learning experience.

I tried the macro and it's very nice. Has a couple sounds to get my
attention

I still would like to know what part of the AddNewRow macro to delete so
that a mess isn't made if someone tabs out of the last row.
Here's the macro again:

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 =
"AddNewRow"
ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
1).Range.FormFields(1).Select
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

After I munged my email address it took awhile, but it cut way down on
the spam. My server has a spam filter that filters email spam and
viruses before they get to my inbox and for awhile I was deleting up to
200 spams a day and about 30-40 viruses in the filter. It was a daily
chore to go in there just to delete the spam and check for legitimate
emails. Now it's down to about 10 spams a day and for 2 days
there haven't been any viruses.