Protection & SpellCheck in Merged Forms

[jlee]

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]

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

 

[jlee]

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










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

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

 

[jlee]

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








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

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

 

[jlee]

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.


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








- Show quoted text -

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

Joy Lee