Wierd Word Macro Crash - HELP!!!!

[Ian Baxter]

I have a Word document, a form with various checkboxes and such, designed to
be a pre-qualification form for Contractors. At first it started with a need
to color the selected answer (of "Yes", "No" or "N/A") in blue. I designed a
macro to do this when the formfield is exited, and with strict naming
conventions for the formfields via bookmarks.

It was then decided that they would like the "form" to automatically "jump"
to a supplementary question if the N/A answer was ticked. Once completed, an
exit macro in the textfield is designed to take the user back to the field
after the "N/A" checkbox.

The code works so long as the user only uses the tab key to move from field
to field in the form. If the user decides to use a mouse and click on the
field after the N/A checkbox instead of using the tab key, Word crashes as
soon as the sub is exited. I am sure it is tied to the movement of the
insertion point via a .SELECT, but have been unable (after several rewrites
and various attempts at workarounds) to identify the cause and solution.

Code:

Public Sub Color_Check()
' This routine goes into the document and colors the checked value
' of either Yes, No, or NA as blue and the others as black
' if NA is chosen, a message is displayed and the user sent to
' the end of the document to fill out additional details
'
' All bookmarks are set as <main>_YES, <main>_NO, <main>_NA, <main>_Jump
and <main>_Back
'
' Initialize our internal values
'
Dim Name_Yes, Name_No, Name_NA As String
Dim IsNA, testVal As Boolean
Dim NameVal, TestName, SelectedNameVal, JumpBookMark, OldMacro As String
Dim BookPos As Range
Dim BookField As FormField
'
' So, only one form field will be selected, we get the value and name
' this way
SelectedNameVal = Selection.FormFields(1).Name

' We need to calculate the <main> value and the other permutations of the
' bookmark
'
NameVal = Left$(SelectedNameVal, InStr(SelectedNameVal, "_") - 1)
Name_Yes = NameVal & "_Yes"
Name_No = NameVal & "_No"
Name_NA = NameVal & "_NA"
'
' Now we determine if there is an NA option for this
' because it appears that there isn't always. We also set the bookmark to
' jump to if there is an "NA" value
'
IsNA = False
JumpBookMark = ""
If (ActiveDocument.Bookmarks.Exists(Name_NA)) Then
IsNA = True
JumpBookMark = NameVal & "_Jump"
End If
'
' And now we know the control that was called, we need to determine its
' state - if checked or not. Unchecking should simply turn it black.
'
' First - is it a checkbox?
If ActiveDocument.FormFields(SelectedNameVal).Type = wdFieldFormCheckBox
Then
testVal = ActiveDocument.FormFields(SelectedNameVal).CheckBox.Value
'
' If the document is protected, we need to unprotect it.
'
ActiveDocument.Unprotect
' If it was checked, then we have to color all the other ones black
and
' uncheck them
If testVal = True Then
' Which ones we check depends on the one we just exited
Select Case SelectedNameVal
Case Is = Name_Yes
ActiveDocument.Bookmarks(Name_No).Range.Font.Color =
wdColorBlack
ActiveDocument.FormFields(Name_No).CheckBox.Value = False
If IsNA Then
ActiveDocument.Bookmarks(Name_NA).Range.Font.Color =
wdColorBlack
ActiveDocument.FormFields(Name_NA).CheckBox.Value = False
End If
Case Is = Name_No
ActiveDocument.Bookmarks(Name_Yes).Range.Font.Color =
wdColorBlack
ActiveDocument.FormFields(Name_Yes).CheckBox.Value = False
If IsNA Then
ActiveDocument.Bookmarks(Name_NA).Range.Font.Color =
wdColorBlack
ActiveDocument.FormFields(Name_NA).CheckBox.Value = False
End If
Case Is = Name_NA
If ActiveDocument.Bookmarks.Exists(Name_Yes) Then
ActiveDocument.Bookmarks(Name_Yes).Range.Font.Color =
wdColorBlack
ActiveDocument.FormFields(Name_Yes).CheckBox.Value = False
End If
If ActiveDocument.Bookmarks.Exists(Name_No) Then
ActiveDocument.Bookmarks(Name_No).Range.Font.Color =
wdColorBlack
ActiveDocument.FormFields(Name_No).CheckBox.Value = False
End If
End Select
'
' Make sure we color the selected bookmark blue
Selection.Font.Color = wdColorBlue
Else
' Here we simply turn the checkbox black
ActiveDocument.Bookmarks(SelectedNameVal).Range.Font.Color =
wdColorBlack
End If
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
'
' We are forced to process this after the SELECT because
' we will end up selecting another area of text after we're
' done.
'
If (SelectedNameVal = Name_NA) And (testVal = True) Then
If (ActiveDocument.Bookmarks.Exists(JumpBookMark)) Then
MsgBox "Additional information is required. Jumping to that
section of the document.", vbOKOnly
ActiveDocument.Bookmarks(JumpBookMark).Select
End If
End If
End If
End Sub

 

[Russ]

Have you put a breakpoint just before you think it crashes and then single
step through the code to pinpoint which line in the code causes problems.
Also what are the versions of Word you will be using this code with?
Have you tried this code on a newer version of Word?

 

[Ian Baxter]

Have you put a breakpoint just before you think it crashes and then single
step through the code to pinpoint which line in the code causes problems.
Also what are the versions of Word you will be using this code with?
Have you tried this code on a newer version of Word?

Of course. It crashes at the "Exit Sub"....

Word 2003.... And since this is a corporate environment, I am not allowed to
have other versions of Word installed. We won't be using Office 2007 for
another 6 months. Feedback from people who have received this form indicates
that the behaviour is the same under Word 2007, but I cannot confirm that.

 

[Ian Baxter]

Okay, I have determined the following:

1. The problem is with the Color_Check macro in the sense that it gets
called "twice"
2. The first time it is called is when the selected "N/A" checkbox exitmacro
is fired.
3. Strangely, the Checkbox the user has clicked on (in order to move forward
in the form) seems to be also "selected" and it appears that the Exitmacro
for that control fires when I execute the
"ActiveDocument.Bookmarks(JumpBookMark).Select" command. This happens despite
the fact that the Selection object is still pointing to the old "N/A"
checkbox.
4. As a result, Word has a furry fit...

If I click on a checkbox which has had the Exitmacro disabled, Word
continues to run just fine.

 

[Ian Baxter]

As a further test, I created a public variable (InMacro) that I set as True
when the macro starts running, False when it completes, and False when the
document is opened.

I added a test to the Color_Check macro (right after the DIM statements)
that would bypass the macro code if InMacro is True.

The macro still crashes Word.

 

[Russ]

It's crashing because the Color_Check macro has been called again before it
is done, right?
So you need to test if it is still active BEFORE it is called again or
temporarily hide objects on the form from user interaction that might call
that macro again before it is finished.


In Doevents VBA help, it mentioned a similar situation that may occur while
using that function:
"Caution Any time you temporarily yield the processor within an event
procedure, make sure the procedure is not executed again from a different
part of your code before the first call returns; this could cause
unpredictable results. In addition, do not use DoEvents if other
applications could possibly interact with your procedure in unforeseen ways
during the time you have yielded control."

 

[Ian Baxter]

Yep, that would match my theory... In other words, VBA code within Word
cannot be re-entrant... Thus, recursive routines would likely fail as well.
So much for being able to write robust object-oriented code in VBA.... *SIGH*

Due to the nature of the document and the large number of formfields, I am
going to have to get really creative.

Thanks for your assistance Russ.

 

[Ian Baxter]

If I set the following macro as the Entry macro (after the modification I
made to create and set a global variable called "InMacro" when Color_Check is
running, the crashes stop.

Sub Check_Running()
Dim SelectedNameVal As String
SelectedNameVal = Selection.FormFields(1).Name
If (InMacro = True) Then
Selection.FormFields(1).ExitMacro = ""
Else
Selection.FormFields(1).ExitMacro = "Color_Check"
End If
End Sub

 

[Russ]

Recursive or multitask. I don't think you could also have another macro
running at the same time doing the same thing to get around the recursive
problem, unless maybe it was acting on a different object or application
object or you could trigger another app.object to launch its macro??
Now the spaghetti is getting too jumbled. ;-)
Good Luck.

 

[Russ]

Ian,
Very nice. I'm glad you posted the code.

If I set the following macro as the Entry macro (after the modification I
made to create and set a global variable called "InMacro" when Color_Check is
running, the crashes stop.

Sub Check_Running()
Dim SelectedNameVal As String
SelectedNameVal = Selection.FormFields(1).Name
If (InMacro = True) Then
Selection.FormFields(1).ExitMacro = ""
Else
Selection.FormFields(1).ExitMacro = "Color_Check"
End If
End Sub

 

[Ian Baxter]

I didn't say it worked....

Further testing shows the original macro stops execution and the remaining
code in Color_Check does not execute.

I am resigned to the fact that I can't make it work as the client wants and
he is going to have to accept my "other" workaround.

 

[Russ]

Ian,
You're not using SelectedNameVal?
Did you want to send it as an argument to Check_Running to make
Check_Running more universal?

Selection.FormFields(SelectedNameVal).ExitMacro

 

[Ian Baxter]

It does not seem to matter. Honestly, I am going to create a small custom
form, query the details, use it to set the value for the formfield requesting
supplementary info and stop jumping around the document.

I am convinced that VBA works well for what it was intended to do - simple
automation. More complex applications require a more robust programming
language.

 

[Russ]

How about having Color_Check throw up a **Userform dialog** message like
"Please Wait" that is modal (i.e. Not modeless) and there is no way for the
user to dismiss the message until the macro is done and the macro takes the
Userform message away.
But that would be an irritant, if it popped up too often ( but then you
might rethink when you do a color check ).

 

[Russ]

Yes, I think must old timers here prefer to use a Userform dialog to gather
information and then plug that data into a document.
And I'm a firm believer in entering data only once to reuse elsewhere.
http://gregmaxey.mvps.org/Repeating_Data.htm

 

[Lene Fredborg]

I tested your code while setting your Color_Check macro as the _entry_ macro
instead of the exit macro and that works.

When you turn on a form field check box, its value has become true already
when the entry macro runs (which originally surprised me). The value remains
true until after the exit macro has run, if any.

Correspondingly, when you turn off a form field check box, its value has
become false already when the entry macro runs and it remains false until
after the exit macro has run, if any.

To see this illustrated, you can try to insert the following code in the
_start_ of your Color_Check macro after having applied it as the _entry_
macro to the three check boxes:

With ActiveDocument
MsgBox "Entry " & vbCr & .FormFields("Name_Yes").CheckBox.Value & _
.FormFields("Name_No").CheckBox.Value & _
.FormFields("Name_NA").CheckBox.Value
End With

Then create a macro, e.g. named "ExitTest", with the same message box code
but with the word "Exit " instead of "Entry ". Assign that macro to the three
check boxes as the _exit_ macro.

Now try turning on and off the check boxes and note the check box values
shown by the message boxes. Remove the exit macro and the additional code in
the Color_Check macro when done.

NOTE:
If one of the check boxes is currently the first form field in the document,
you could insert some other form field before it just to make sure that the
check box is not automatically selected when the document opens since this
will fire the entry macro - and nothing will happen if the user clicks the
check box (because it is already selected).

If you insert the line
Application.ScreenRefresh
before the code that displays the message at the end of your macro, you will
prevent the user form seeing 2 checked check boxes when the dialog box is
displayed in case the user had previously checked the Yes or No check box.

--
Regards
Lene Fredborg
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word

 

[Ian Baxter]

Wow,

It is going to take some re-writing, but I might be able to make that work.
Thanks for the response... I'll keep you all posted.

I tested your code while setting your Color_Check macro as the _entry_ macro
instead of the exit macro and that works.

When you turn on a form field check box, its value has become true already
when the entry macro runs (which originally surprised me). The value remains
true until after the exit macro has run, if any.

........