macro to copy a table

[Horatio J. Bilge, Jr.]

I have a template that teachers will use to record progress on student goals.
Each goal appears in a separate table, but the number of goals for each
student will be different. Using the macro recorder as a starting point, I
wrote a macro to copy the goal table and paste it at the end of the document.
When I get this worked out, I plan on having the macro ask for user input to
determine how many copies of the table to make.

I have a couple of issues that I can't figure out:
1. I am using a CommandButton to call the macro, but after finishing the
macro, I need the button to be invisible (or even remove it).
2. In the goal table, there are several check boxes. When the macro runs, it
adds a check to the first check box in every table (including the original
table that was copied).

 

[Jay Freedman]

For the first issue, I'll suggest either of two solutions:

(a) Rename your macro as AutoNew() and remove the command button from the
template. See http://word.mvps.org/faqs/macrosvba/DocumentEvents.htm. An
AutoNew macro in the template will run whenever the File > New command is
used to make a document based on the template. (Do not put the macro into
Normal.dot!)

(b) Instead of using a command button in the template's body, put a button
on a toolbar, specifying that the button is stored in the template (so it's
visible only when the current document is based on that template). See
http://www.word.mvps.org/FAQs/Customization/AsgnCmdOrMacroToToolbar.htm. The
toolbar could be an existing one, or a new floating toolbar stored in the
template.

The second issue can't be solved without knowing what the macro does. Can
you post the code?

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

 

[Horatio J. Bilge, Jr.]

Sorry, I meant to attach the code. I haven't added the user input piece yet
(for how many times to paste table); here is the code I have so far. It works
as expected, except that it is adding a check to the first check box in the
table.

Sub Copy_Table()
ActiveDocument.Unprotect
ActiveDocument.Tables(1).Select
Selection.Copy
Selection.EndKey Unit:=wdStory
Selection.TypeParagraph
Selection.Paste
ActiveDocument.Protect Type:=wdAllowOnlyFormFields
End Sub

I added the AutoNew() macro, and it works great.
Sub AutoNew()
Copy_Table
End Sub

 

[Jay Freedman]

I tried your code (in Word 2003, in case that makes a difference), but it
didn't cause any check boxes to be checked. I'm not sure what's happening on
your computer, although I suspect it has something to do with the
Tables(1).Select.

There are a couple of ways to avoid having to select the table at all. One
is to use a Range object to "copy" the first table without going through the
clipboard. Try this:

Sub x()
Dim myRg As Range
Set myRg = ActiveDocument.Range

If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect
End If

With myRg
.InsertAfter vbCr
.Collapse wdCollapseEnd
.FormattedText = _
ActiveDocument.Tables(1).Range.FormattedText
End With

ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True
End Sub

Another method would be to store a copy of the first table as an AutoText
entry in the same template, and then have the macro insert that AutoText as
many times as needed.

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

 

[Horatio J. Bilge, Jr.]

Thanks for the help - I tried the macro you suggested, and it worked fine
with the checkboxes. I also tried adding the table as an AutoText entry, but
I get an error on the .InsertAutoText line:
Sub CopyTable_AutoText()
Dim myRg As Range
Set myRg = ActiveDocument.Range
With myRange
.InsertAfter vbCr
.Collapse wdCollapseEnd
.InsertAutoText ("Goal_Table")
End With
End Sub

With either method, how can I control how many times to copy it? I created a
UserForm where a user can define how many Goals there will be. I used a combo
box with numbers 1-10, but I am open to other methods. I was thinking that
the combo box selection would be passed to the Copy_Table() macro to define
how many times to copy it.

One last thing (I hope)... the first cell in each table gives the goal #
(e.g., "Goal #1"). I wrote a macro to change the text, but I won't know how
many tables there will be:
With ActiveDocument
.Tables(1).Cell(1, 1).Select
Selection.Text = "Goal # 1"
' repeat code for each table
End With

I tried a for-each loop, but I got an error on the .Cell().Select line. I'm
not sure how to add the goal # - the first table should be Goal #1, the
second one is Goal #2, etc.:
Dim aTable As Table
For Each aTable In ActiveDocument.Tables
.Cell(1, 1).Select
Selection.Text = "Goal #"
Next aTable

~ Horatio

 

[Jay Freedman]

This is rather a large pile of issues...

For inserting an AutoText entry, you can use either of two methods, but you seem
to have tried to blend them together and gotten garbage.

A. If you want to use the .InsertAutoText method, the idea is that the macro
first types the entry's name into the document as regular text, and then it
calls the .InsertAutoText without any parameters. This is the exact analog of a
user typing the entry's name in the text and pressing F3 to get the replacement.
The code would go

With myRg
.InsertAfter vbCr & "Goal_Table"
.Collapse wdCollapseEnd
.InsertAutoText
End With

B. The other method is more object-oriented and less like a phantom user typing
on the keyboard. The ActiveDocument object has a property called
AttachedTemplate, which in turn has a collection property called
AutoTextEntries. To get the particular entry you want, use the entry's name as
the index into that collection. The entry then has an .Insert method, to which
you pass the range where the entry should be inserted:

With myRg
.InsertAfter vbCr
.Collapse wdCollapseEnd
End With
ActiveDocument.AttachedTemplate.AutoTextEntries("Goal_Table").Insert _
Where:=myRg


Moving on to how you make multiple copies... Do you know how to get the selected
number to be passed back from the Userform to the macro? If not, that can be the
topic of the next round of posts. Assuming that number has been stored in an
integer variable named nRepeats, you can do this (for this example I'll just
assign the number):

Sub CopyTable_AutoText()
Dim nRepeats As Integer
Dim nCounter As Integer
Dim myRg As Range
nCounter = 10 ' get this from Userform
For nCounter = 1 To nRepeats
Set myRg = ActiveDocument.Range
With myRg
.InsertAfter vbCr & "Goal_Table"
.Collapse wdCollapseEnd
.InsertAutoText
End With
Next nCounter
End Sub


Finally, dealing with inserting the goal number in the first cell of each
table... This is a slight extension of the preceding code. Note that you already
have the table number as you insert each table, in the loop variable nCounter.
You don't have to -- and should not -- select anything just to put text in a
cell. The object-oriented way to think about it is that the table that was just
inserted is always the last table in the document, which is represented by
ActiveDocument.Tables.Last. That table has a .Cell(1,1), which has a .Range,
which has .Text. You just want to assign the desired string to that .Text:

Sub CopyTable_AutoText()
Dim nRepeats As Integer
Dim nCounter As Integer
Dim myRg As Range
nCounter = 10 ' get this from Userform
For nCounter = 1 To nRepeats
Set myRg = ActiveDocument.Range
With myRg
.InsertAfter vbCr & "Goal_Table"
.Collapse wdCollapseEnd
.InsertAutoText

ActiveDocument.Tables.Last.Cell(1, 1).Range.Text = _
"Goal # " & nCounter
End With
Next nCounter
End Sub

 

[Horatio J. Bilge, Jr.]

I really appreciate all your help. Your code for inserting the AutoText works
great, but I get a "Method or data member not found" error when it when it
tries to insert "Goal # " into the table. VBA Help says the the Last property
doesn't apply to the Tables object.

I have been trying to figure out how to pass the userform selection to the
macro, but I'm not having any luck. I have done similar things with Excel,
but I typically assigned the value to a cell in the workbook, and then used
the value of that cell in the macro.

Here is what I have so far (I have disabled the part that writes the "Goal #
" for now):
Sub AutoNew()
Dim nRepeats As Integer
Dim nCounter As Integer
Dim myRg As Range

frmGoals.Show ' the form has combo box and submit, but right now, submit
just hides the form

nRepeats = 10 ' get this from Userform
For nCounter = 1 To nRepeats
Set myRg = ActiveDocument.Range
With myRg
.InsertAfter vbCr & "Goal_Table"
.Collapse wdCollapseEnd
.InsertAutoText
End With

' ActiveDocument.Tables.Last.Cell(1, 1).Range.Text = _
' "Goal # " & nCounter
Next nCounter
End Sub

 

[Jay Freedman]

AutoText works great, but I get a "Method or data member not found"

error when it when it tries to insert "Goal # " into the table. VBA
Help says the the Last property doesn't apply to the Tables object.

Oops, I tried to take a shortcut (you know, the longest distance between two
points). Try it this way:

ActiveDocument.Tables(ActiveDocument.Tables.Count).Cell(1, 1) _
.Range.Text = "Goal # " & nCounter

To pass a number from the userform back to the macro that calls the .Show,
do these steps:

- In the code window of the userform, at the top before the first Sub
statement, declare a public variable

Public returnValue As Integer

The name you choose is up to you, but it must be Public (which means it's
available outside the userform itself).

- In the Click procedure of the OK button, assign the numerical value of the
combo box to this public variable and then hide the userform, which returns
control to the macro:

Private Sub btnOK_Click()
returnValue = Val(ComboBox1.Value)
Me.Hide
End Sub

- In the calling macro, instead of just showing the userform, make an object
from it and show the object. When control returns there, the public variable
is available as a property of the object, until you destroy the object by
setting it to Nothing:

Sub Copy_Table()
Dim nRepeats As Integer
Dim dlgGoal As frmGoals
' ... other Dim statements

Set dlgGoal = New frmGoals ' must use Set keyword with objects

dlgGoal.Show

' after userform hides, control comes here...
nRepeats = dlgGoal.returnValue

Set dlgGoal = Nothing ' clean up

For nCounter = 1 To nRepeats
' etc.
End Sub

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

 

[Horatio J. Bilge, Jr.]

Passing the variable doesn't seem to be working for me. There is no error
message, but the tables aren't being copied, and the text isn't being
changed. Here is the complete code that I am using. Code for the userform is
below.
Option Explicit
Sub AutoNew()
Dim nRepeats As Integer
Dim nCounter As Integer
Dim myRg As Range
Dim dlgGoal As frmGoals

Set dlgGoal = New frmGoals
frmGoals.Show
nRepeats = dlgGoal.returnValue
Set dlgGoal = Nothing 'clean up

For nCounter = 1 To nRepeats
Set myRg = ActiveDocument.Range
With myRg
.InsertAfter vbCr & "Goal_Table"
.Collapse wdCollapseEnd
.InsertAutoText
End With
ActiveDocument.Tables(ActiveDocument.Tables.Count).Cell(1, 1) _
.Range.Text = "Goal # " & nCounter
Next nCounter
End Sub

And for the UserForm:
Option Explicit
Public returnValue As Integer
Private Sub UserForm_Initialize()
With combGoals
.AddItem 1
.AddItem 2
.AddItem 3
' continue up to 10 items
End With
End Sub

Private Sub cmdSubmit_Click()
returnValue = Val(combGoals.Value)
Me.Hide
End Sub

 

[Jay Freedman]

You missed a small detail: You still called .Show on frmGoals, but it should
now be called on the specific object dlgGoal. They aren't the same thing --
think of frmGoals as a template, and dlgGoal as a specific object built by
copying the template. Since dlgGoal hasn't been shown, its returnValue
property never changes from the initial value of 0. When you change it to
dlgGoal.Show you should see the correct results.

One more hint: In the Initialize procedure, after you assign the items, set

combGoals.ListIndex = 0

so the combo box will appear with the first item (the "1") already in the
box instead of a blank. That avoids the possibility of someone clicking the
Submit button immediately and transferring the blank, which the Val function
will convert to 0. You might also want to set

combGoals.MatchRequired = True

so the user must pick one of the supplied values instead of typing in some
unwanted value.

 

[Horatio J. Bilge, Jr.]

Works great! I really appreciate the time you spent helping me, and your
explanations were very clear and thorough.

Thank you,
~ Horatio

 

[Jay Freedman]

Works great! I really appreciate the time you spent helping me, and your
explanations were very clear and thorough.

Thank you,
~ Horatio