Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / March 2007

Tip: Looking for answers? Try searching our database.

Protection & SpellCheck in Merged Forms

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jlee@valleywater.org - 03 Mar 2007 01:40 GMT
Hi,

I tried using the codes provided at the link below, for mail merge for
MS Word form document.

http://support.microsoft.com/?kbid=211308

They worked, except I was unable to re-protect the main form as before
the merge, and to switch back to the new merged file.  At the site
above, I see the strings of codes specifically written for that, but
they simply don't work when I used in my form.

The new merged file is also a form with a few form fields, of which
some are already populated from the data source, by the mail merge,
and some need to be filled in by the users.  Another problem is they
need to spell check for the part they fill out.  The new merged form
is password protected at the time of merge.  So I need to figure out a
way for the users to run  a spellcheck with their form still
protected.  I also checked out the codes for that, but can't figure
out how to include those codes in the main form so that after the
merge, they can be activated in the new merged form file.  Do I make
sense?

Can any one help me on my issues?

Joy
Peter Jamieson - 04 Mar 2007 11:33 GMT
> except I was unable to re-protect the main form as before
> the merge, and to switch back to the new merged file.

The code works OK with a simple example I created here using Word 2003, so
my best guess is that when the code gets to this point, the ActiveDocument
is not the one it should be:

' Reprotect the main mail merge document.
ActiveDocument.Protect Password:="", NoReset:=True, _
  Type:=wdAllowOnlyFormFields

However, it's difficult to see why that might be so. There are a couple of
things ytou could do to investigate:
a. learn how to use the debug facilities within VBA (breakpoints, stepping
through code etc. with the Debug toolbar)
b. put msgbox or debug.print statements into the code to examine the values
of the variables involved. msgbox makes a message box pop up, whereas
debug.print displays the results in the Immediate Window (in the VBA
editor), which you can enable using ctrl-G in the VBA Editor.

e.g. I might start with the following:

put

MsgBox "swindowmain:" & sWindowMain

before

' Reactivate the main merge document.
Windows(sWindowMain).Activate

and put

MsgBox "activedocument: " & ActiveDocument.Name

before

' Reprotect the main mail merge document.
ActiveDocument.Protect Password:="", NoReset:=True, _
  Type:=wdAllowOnlyFormFields

and see what results you get.

On the subject of spell checking, I think you have to play a similar game,
i.e. use a macro to unprotect the document, spell check, then reprotect, and
make that macro available to your users by associating it with a toolbar
button, a menu option, or a keyboard shortcut, e.g. in Tools|Customize.
However, the macro really has to be either in the normal.dot template, or if
your mail merge main document is attached to another template, put it in
that template. Putting it in the mail merge main document itself will not
work.

You could use a macro like

Sub mySpellCheck()

With ActiveDocument
 If .ProtectionType <> wdNoProtection Then
   .Unprotect
 End If
 .CheckSpelling
 .Protect Password:="", NoReset:=True, _
  Type:=wdAllowOnlyFormFields
End With

End Sub

Peter Jamieson
> Hi,
>
[quoted text clipped - 22 lines]
>
> Joy
jlee@valleywater.org - 05 Mar 2007 23:10 GMT
On Mar 4, 3:33 am, "Peter Jamieson" <p...@KillmapSpjjnet.demon.co.uk>
wrote:
> > except I was unable to re-protect the main form as before
> > the merge, and to switch back to the new merged file.
[quoted text clipped - 95 lines]
>
> - Show quoted text -

Thank you for your quick response!  I've tried what you told me to
try . . . and everything works up to the reprotection of the main
merge doc, or I'd say "Find and replace . . ." part right before that,
and the active document is the main merge doc.  The MsgBox commands
don't work after the 2nd "Find and replace . . ." part.  I'm trying to
learn debugging as you said, but I'm so novice at this point.  So are
you saying it could be something in the codes or something I put
that's prohibiting them to work properly?  I was wondering if this is
supposed to not work at all, after the doc has gone through the
merge . . . .

JoyLee
Peter Jamieson - 06 Mar 2007 08:11 GMT
If the message boxes are not appearing then the MsgBox statements are almost
certainly not being executed, in which case I would guess that an error is
being "raised", probably in the doFindReplace Sub. If you haven't got the
hang of the debug stuff and single-stepping, you could try
a. putting MsgBox statements at the beginning and end of that Sub
b. comment out the line at the top of the Sub
PreserveMailMergeFormFieldsNewDoc that says

On Error GoTo ErrHandler

(i.e. put a quote before it:)

' On Error GoTo ErrHandler

 c. optionally, putting some additional error trapping into Sub
doFindReplace, e.g.

put

On Error Goto problem

before

' Go to top of document.
Selection.HomeKey Unit:=wdStory

and put

Exit Sub
problem:
 Msgbox Err.Number & ": " & Err.Description

before

End Sub

and see what happens.

Peter Jamieson

> On Mar 4, 3:33 am, "Peter Jamieson" <p...@KillmapSpjjnet.demon.co.uk>
> wrote:
[quoted text clipped - 118 lines]
>
> JoyLee
jlee@valleywater.org - 06 Mar 2007 18:44 GMT
On Mar 6, 12:11 am, "Peter Jamieson" <p...@KillmapSpjjnet.demon.co.uk>
wrote:
> If the message boxes are not appearing then the MsgBox statements are almost
> certainly not being executed, in which case I would guess that an error is
[quoted text clipped - 164 lines]
>
> - Show quoted text -

Wow!  Now the form is working!   The MsgBox commands are working also,
with ActiveDocument switching back and forth, and at the end of merge,
an error message appears:  9: Subscript out of range.   So if I don't
clear this #9 error, the message box will keep popping up even though
the form's working, right?  That would really confuse the users.  What
does this mean?  I'm checking Help menu but don't see any explanation
there.  Thank you so much for your help!!!

Joy Lee
Peter Jamieson - 07 Mar 2007 10:13 GMT
> What
> does this mean?

An example of what it means is that when Word executes

     ReDim Preserve fFieldText(1, iCount + 1)

it says the array variable fFieldText should now have 1 columns and iCount +
1 rows, and keep any data that you already put in there. Well in fact,
because array variable indexes start by default at 0 rather than 1, it
actually means fFieldText should have 2 columns and iCount+2 rows

So suppose iCount is 2, then this statement creates an array like this:

fFieldText(0,0)    fFieldText(1,0)
fFieldText(0,1)    fFieldText(1,1)
fFieldText(0,2)    fFieldText(1,2)
fFieldText(0,3)    fFieldText(1,3)

If the program now tries to put something in fFieldText(0,4) the second
"index" (4) is out of range and the error is raised.

However, right now I can't see where the code could be doing that wrong. The
sample code you are using creates one more row than it actually needs, and
it does reference that final row in the following code, but it shouldn't do
any harm even if it does. If the code had "Option Base 1" at the top - it
changes arrays so they start at e.g. fFieldText(1,1) - the the code would
not get as far as it does.

> So if I don't
> clear this #9 error, the message box will keep popping up even though
> the form's working, right?

Right

> That would really confuse the users.

Of course. It's a question of identifying where the code is failing and why,
then fixing the problem, then removing any debug code such as MsgBox you
used to find the error. The more you can apply what you are learnign locally
to pin down the fault the better because it's not particularly easy to do it
at a distance because the approach has to be "try this..., try that".

In this case I would probably change Sub doFindReplace to

Sub doFindReplace(iCount As Integer, fField As FormField, _
  fFieldText() As String)

On Error Goto problem

' Go to top of document.
Selection.HomeKey Unit:=wdStory

' Initialize Find.
Selection.Find.ClearFormatting

With Selection.Find
  .Forward = True
  .Wrap = wdFindContinue
  .Format = False
  .MatchCase = False
  .MatchWholeWord = False
  .MatchWildcards = False
  .MatchSoundsLike = False
  .MatchAllWordForms = False

' Use j to find out where it went wrong
j = 1

  ' Loop form fields count.
   For i = 0 To iCount

j = 2
     ' Execute the find.
     Do While .Execute (FindText:="<" & fFieldText(1, i) _
        & "PlaceHolder>") = True

j = 3
        ' Replace the placeholder with the form field.
        Set fField = Selection.FormFields.Add _
           (Range:=Selection.Range, Type:=wdFieldFormTextInput)

j = 4
        ' Restore form field contents and bookmark name.
        fField.Result = fFieldText(0, i)
j = 5
        fField.Name = fFieldText(1, i)

     Loop

j = 5
     ' Go to top of document for next find.
     Selection.HomeKey Unit:=wdStory

  Next
End With

Exit Sub
problem:
 Msgbox Err.Number & ": " & Err.Description

 Msgbox "iCount: " & iCount & ", i: " & i & ",j: " & j

End Sub

Peter Jamieson
> On Mar 6, 12:11 am, "Peter Jamieson" <p...@KillmapSpjjnet.demon.co.uk>
> wrote:
[quoted text clipped - 189 lines]
>
> Joy Lee
jlee@valleywater.org - 07 Mar 2007 21:51 GMT
On Mar 7, 2:13 am, "Peter Jamieson" <p...@KillmapSpjjnet.demon.co.uk>
wrote:
> > What
> > does this mean?
[quoted text clipped - 300 lines]
>
> - Show quoted text -

Thank you so much for all your help!  Everything works beautifully now
~  You've saved me ^^

Joy Lee
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.