Range Madness and Other Frustrations

[Greg Maxey]

This is a very long post. I have had fits today working with ranges
and an on exit event. My challenge (perhaps your childs play) was to
create a conditional additional question in a form.

Example:

Why did the chicken cross the road? "Dropdown field containing
multiple choices"

If the selection made above was "To get the other side" Then display a
follow-up question:

Did she say why she wanted to cross the road: "Dropdown field
containing yes and no"

I created the first question and dropdown field using "Question" as the
bookmark name.

From the beginning, I figured that using a stored AutoText entry of the

follow-up question and dropdown field would be the easiest approach.

I created the follow-up question and dropdown field using "FUA" as the
bookmark name. I then stored the follow-up question and formfield as
AutoText "FUQ."

I used a bookmark "FUQ" to designate the placement of the conditional
question.

Since the FUQ needed to be displayed or hidden depending on the
condition, I realized that I would need to write either the AutoText
FUQ or "" to the bookmark range and redefine the range with each
change.

My first coding attempt was:

Sub OnExitDropDown()
Dim oFF As FormFields
Dim oRng As Word.Range
Set oFF = ActiveDocument.FormFields
Select Case
oFF("Question").DropDown.ListEntries(oFF("Question").DropDown.Value).Name
Case Is = "Yes"
ActiveDocument.Unprotect
Set oRng = ActiveDocument.Bookmarks("FUQ").Range
oRng.Text = NormalTemplate.AutoTextEntries("FUQ")
ActiveDocument.Bookmarks.Add "FUQ", oRng
ActiveDocument.Protect wdAllowOnlyFormFields, True
Case Else
ActiveDocument.Unprotect
Set oRng = ActiveDocument.Bookmarks("FUQ").Range
oRng.Text = ""
ActiveDocument.Bookmarks.Add "FUQ", oRng
ActiveDocument.Protect wdAllowOnlyFormFields, True
End Select
End Sub

To my dismay, I realized that "oRng.Text =" must be taken literally as
the displayed result was:

"Did he say why he crossed the road? * FORMDROPDOWN **" and not the
questions text with a functional formfield :-(

I monkey around with the oRng object a bit but I couldn't find a method
that would allow me to put the actual working content of the AutoText
entry in oRng.

Question 1 - Is there a method to put something other than literal text
in a range?

Giving up that approach, I next thought about using a find and replace
with autotext routine that me and friend Graham Mayor worked out
sometime ago.

My approach was to write a "flag" within the bookmark range then use
the find replace routine to replace the flag with the AutoText entry.

I used this code:

Sub OnExitDropDown()
Dim oFF As FormFields
Dim oRng As Word.Range
Set oFF = ActiveDocument.FormFields
Select Case
oFF("Question").DropDown.ListEntries(oFF("Question").DropDown.Value).Name
Case Is = "Yes"
ActiveDocument.Unprotect
Set oRng = ActiveDocument.Bookmarks("FUQ").Range
oRng.Text = "*@*"
ActiveDocument.Bookmarks.Add "FUQ", oRng
RWAT
ActiveDocument.Protect wdAllowOnlyFormFields, True
Case Else
ActiveDocument.Unprotect
Set oRng = ActiveDocument.Bookmarks("FUQ").Range
oRng.Text = ""
ActiveDocument.Bookmarks.Add "FUQ", oRng
ActiveDocument.Protect wdAllowOnlyFormFields, True
End Select
End Sub

And

Sub RWAT()
Dim oSPRng As Word.Range
Dim oSrchRng As Range
Dim pFN As String
'Create a scratch pad
Set oSPRng = Documents.Add.Range
NormalTemplate.AutoTextEntries("FUQ").Insert oSPRng
'Cut the inserted entry to the clipboard
oSPRng.End = ActiveDocument.Range.End - 1
oSPRng.Cut
ActiveDocument.Close wdDoNotSaveChanges
'crumple up the scratch pad
'Replace found text with the clipboard contents.
Set oSrchRng = ActiveDocument.Bookmarks("FUQ").Range
With oSrchRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "*@*"
.Replacement.Text = "^c"
.Execute Replace:=wdReplaceOne
If .Found = True Then MsgBox "Flag found" 'Added for testing
End With
End Sub

Again to my dismay and despite the Msgbox displaying "Flag found," the
result in the document at the bookmark was:

*@*

I stepped through the code repeatedly and each time all indications
where that the flag was found, the replacement executed, but the result
was the same as shown above :-(

I fiddled around with this and soon realized (but can't explain why)
that if I set the flag as "*@*" and the then searched for "*@" the
result at the bookmark was:

Did he say why he crossed the road? "a fully functional dropdown"*

Viewing this as a minor concession, I changed the flag to:

oRng.Text = "*@ " 'note the trailing space

and this left just a single extra space at the end of the bookmark
range instead of "*."

Question 2 - Can someone explain the behavior described above? Why
when the found range includes the entire bookmark range does the
replacement fail?

Thinking that I had found a solution, I set my macro to run on exit
from the "Question" formfield.

My euphoria was quickly dashed. While the code worked perfectly when I
a) stepped through it with the editor, and b) ran it directly from the
editor, it failed when triggered by tabbing out of the formfield.

It was generating a RTE 4198 "Command failed" while attempting to
execute this line of code in the RWAT procedure:

ActiveDocument.Close wdDoNotSaveChanges

Since it ran normally from the editor, I suspected that this must have
something to do with the field controlling the activedocument until the
procedure is completed. I tried all I know to create a specific
document object e.g.,

Dim oDoc as Word.Document
Set oDOC = Documents.Add
......
oDoc.Close

but I got the same error regardless of what I tried.

Question 3 - Can anyone explain the behavior described above?

Sticking with the find and replace autotext theme and convinced that
attempting to close the scratch pad was the stumbling block, I elected
to add a scratch pad at the end of the active document and then delete
it.

This is the RWAT code (note the main code stayed the same):

Sub RWAT()
Dim oSPRng As Word.Range
Dim oSrchRng As Range
'Create a note area
Set oSPRng = ActiveDocument.Range
oSPRng.Start = ActiveDocument.Range.End
'Write the AutoText entry
NormalTemplate.AutoTextEntries("FUQ").Insert oSPRng
'Cut the inserted entry to the clipboard
oSPRng.End = ActiveDocument.Range.End - 1
oSPRng.Cut
'tear off the scratch pad
'Replace flag with the clipboard contents.
Set oSrchRng = ActiveDocument.Bookmarks("FUQ").Range
With oSrchRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "*&"
.Replacement.Text = "^c"
.Execute Replace:=wdReplaceOne
End With
End Sub

This appears to be working as expected but I have not fully tested the
possible effects on the document from creating and then deleting the
note area.

Additionally, I learned that I could get rid of the extraneous space
within the bookmark by redefining the bookmark again using oRgn after
it contained the functioning AutoText and I added code to select the
new dropdown field:

Final version:

Sub RWAT()
Dim oSPRng As Word.Range
Dim oSrchRng As Range
'Create a note area
Set oSPRng = ActiveDocument.Range
oSPRng.Start = ActiveDocument.Range.End
'Write the AutoText entry
NormalTemplate.AutoTextEntries("FUQ").Insert oSPRng
'Cut the inserted entry to the clipboard
oSPRng.End = ActiveDocument.Range.End - 1
oSPRng.Cut
'tear off the scratch pad
'Replace flag with the clipboard contents.
Set oSrchRng = ActiveDocument.Bookmarks("FUQ").Range
With oSrchRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "*&"
.Replacement.Text = "^c"
.Execute Replace:=wdReplaceOne
End With
End Sub

Question 4 - This seems like a brutally complicated process yet appears
that it could be applied relatively to several scenarios.

An earlier thought this morning was to simply insert the AutoText at
the bookmark then move the bookmark range and redefined it:

This code worked but I didn't like the idea of having to "know" what
the last text character was in the formfield (i.e., the "?")

Sub OnExitDropDown1()
Dim oFF As FormFields
Dim oRng As Word.Range
Set oFF = ActiveDocument.FormFields
Select Case
oFF("DropDown1").DropDown.ListEntries(oFF("DropDown1").DropDown.Value).Name
Case Is = "To get to the other side"
ActiveDocument.Unprotect
Set oRng = ActiveDocument.Bookmarks("FUQ").Range
NormalTemplate.AutoTextEntries("FUQ").Insert oRng
oRng.MoveEndUntil Cset:="?", Count:=wdForward
oRng.MoveEnd wdWord, 2
ActiveDocument.Bookmarks.Add "FUQ", oRng
ActiveDocument.Protect wdAllowOnlyFormFields, True
ActiveDocument.Bookmarks("FUA").Range.FormFields(1).Select
Case Else
ActiveDocument.Unprotect
Set oRng = ActiveDocument.Bookmarks("FUQ").Range
oRng.Text = ""
ActiveDocument.Bookmarks.Add "FUQ", oRng
ActiveDocument.Protect wdAllowOnlyFormFields, True
End Select
End Sub

However after all this effort I am beginning to think that I should
have stayed with this idea. LOL

Interested in any other ideas and simplifications.

Thanks.

 

[Dian D. Chapman, MVP]

You're right...ranges are a PITA. I avoid them at all cost. ;-)

I had to do a LOT of AutoText and File inserts in this project:
http://www.mousetrax.com/Consulting_Solutions.html#reviews

So, although I have all separate subs for InsertAutoText, Rewrap and
GoToBookmark in the modMain for this...I've pulled the code out into
static code below so it can be run in one shot and I commented out the
other stuff in the sub that doesn't apply here.

Rather than messing with ranges, I went the weenie route<g>. Since you
KNOW the cursor will end up at the end of the insert, you can move one
char back to get to the end of the previous line. Then you select back
to the original bookmark. Now you have the current insert selected.

Then just delete the original bookmark and, since you've passed that
name into a variable, you still have it within the procedure, so you
can readd it now that you have the entire area selected. This rewraps
the content with the same bookmark so the info can be changed if
needed in an update (the project was an employee review that would
most likely be often updated).

And if you don't NEED the bookmark cos' the info is NO, you can just
delete it...or do what I did...style that bookmark para with a bogus
1pt font so no one but you will even know it's there cos' it's too
tiny to see. ;-)

Jay or Bill could probably come up with something slicker...but this
worked great for me for the MANY inserts and updates that were needed
for the several pages of review data needed in the docs.


Sub InsertAndWrapAutoText(strBookmark As String, _
strAutoText As String)
'(e-mail address removed)
'PURPOSE: inserts autotext content into bookmarked location
'rewraps w/same bookmark
'**********************
'On Error GoTo Handler

'unlock doc
'modMain.ToggleFormLock

'go to bookmark location
Selection.GoTo What:=wdGoToBookmark, Name:=strBookmark

'insert autotext
ActiveDocument.AttachedTemplate.AutoTextEntries(strAutoText) _
.Insert Where:=Selection.Range, RichText:=True

'move cursor back one char to end of this last insertion point
Selection.MoveLeft unit:=wdCharacter, Count:=1

'turn on EXT (selection function)
Selection.Extend

'move back up to starting bookmark, which selects this insert
Selection.GoTo What:=wdGoToBookmark, Name:=strBookmark

'delete original single bookmark
ActiveDocument.Bookmarks(strBookmark).Delete

'rewrap w/double bookmark
With ActiveDocument.Bookmarks
.Add Range:=Selection.Range, Name:=strBookmark
End With

'relock doc
'modMain.ToggleFormLock

'GoTo done
'Handler:
' gstrErrorString = "InsertAndWrapAutoText:modMain"
' modErrorHandler.ErrorLog gstrErrorString
'done:

End Sub

Dian D. Chapman
Technical Consultant, Microsoft MVP
MOS Certified, Editor/TechTrax

Free MS Tutorials: http://www.mousetrax.com/techtrax
Free Word eBook: http://www.mousetrax.com/books.html
Optimize your business docs: http://www.mousetrax.com/consulting
Learn VBA the easy way: http://www.mousetrax.com/techcourses.html

 

[Greg Maxey]

Dian,

Thanks. Your complete abandonment of range is a bit too radical for me, but
I was able to put together a composite that seems to work really well:

Sub OnExitQuestion()
Dim oRng As Word.Range
Dim oFF As FormFields
With ActiveDocument
If .ProtectionType <> wdNoProtection Then .Unprotect
Set oFF = .FormFields
Select Case oFF("PriAnswer").Result
Case Is = "To get to the other side"
'If the secondary question and formfield already exists then skip
creation. If not, create the secondary question and redefine the bookmark
If Not .Bookmarks.Exists("SecAnswer") Then
Selection.GoTo What:=wdGoToBookmark, Name:="FUQ"
.AttachedTemplate.AutoTextEntries("FUQ").Insert Selection.Range, True
Set oRng = Selection.Range
oRng.Start = .Bookmarks("FUQ").Start
.Bookmarks.Add "FUQ", oRng
End If
.Bookmarks("SecAnswer").Range.FormFields(1).Select
Case Else
Set oRng = .Bookmarks("FUQ").Range
oRng.Text = ""
.Bookmarks.Add "FUQ", oRng
End Select
If .ProtectionType <> wdAllowOnlyFormFields Then
.Protect wdAllowOnlyFormFields, True
End If
End With
End Sub

If anyone else is ready this then I am still interested in explanations for
the seemingly wierd behaviour with decribed earlier.

 

[Jay Freedman]

I usually have just the opposite opinion -- I'd much rather work with
ranges than with the Selection. But this one has whupped my butt. I
think there's a bug lurking in here somewhere.

This macro *should* handle Greg's assignment, and it almost does:

Sub OnExitDropDown()
Dim oDoc As Document
Dim oTmpl As Template
Dim oRng As Range
Dim ff As FormField

Set oDoc = ActiveDocument
Set oTmpl = oDoc.AttachedTemplate
Set ff = oDoc.FormFields("Question")
Set oRng = oDoc.Bookmarks("FUQ").Range

With oDoc
If .ProtectionType <> wdNoProtection Then
.Unprotect
End If

If ff.DropDown.ListEntries(ff.DropDown.Value).Name = _
"To get to the other side." Then
oRng = oTmpl.AutoTextEntries("FUQ") _
.Insert(where:=oRng, RichText:=True)
Else
oRng.Text = ""
End If

.Bookmarks.Add Name:="FUQ", Range:=oRng
.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End With
End Sub

But when the AutoTextEntry is inserted, I always get two copies of it,
one with an "unrecognized character" square instead of the working
dropdown, and then one with the working dropdown. The returned range,
which then gets bookmarked, contains only the nonworking copy.

If I insert the entry from the Insert > AutoText menu, or even if I
run the equivalent VBA statement in the Immediate window, I just get
the working copy. I just can't seem to get the full macro to behave
right. <shrug>

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

 

[Greg Maxey]

Jay,

Weird isn't it :-(

I merged your code with the code I worked out after reading Dian's
suggestions and came up with this:

Sub OnExitDropDown()
Dim oDoc As Document
Dim oTmpl As Template
Dim oRng As Range
Dim ff As FormField
Set oDoc = ActiveDocument
Set ff = oDoc.FormFields("PriAnswer")
Set oRng = oDoc.Bookmarks("FUQ").Range
With oDoc
If .ProtectionType <> wdNoProtection Then .Unprotect
If ff.Result = "To get to the other side" Then
If Not .Bookmarks.Exists("SecAnswer") Then
oRng.Select
.AttachedTemplate.AutoTextEntries("FUQ").Insert Selection.Range, True
Set oRng = Selection.Range
oRng.Start = .Bookmarks("FUQ").Start
.Bookmarks.Add "FUQ", oRng
End If
.Bookmarks("SecAnswer").Range.FormFields(1).Select
Else
oRng.Text = ""
.Bookmarks.Add Name:="FUQ", Range:=oRng
End If
.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End With
End Sub

This is seems to be working good, but I can't explain the weird behaviour
using only ranges.

 

[Dian D. Chapman, MVP]

HA! Purist! ;-)

Dian ~

 

[Dian D. Chapman, MVP]

Cool beans, Greg!

But as I said, mine wasn't all that elegant, but it DID get me through
the project in a short time and worked great.

But I will steal your code now to add to my library!

Dian ~

 

[Jean-Guy Marcil]

Greg Maxey was telling us:
Greg Maxey nous racontait que :

This is a very long post. I have had fits today working with ranges
and an on exit event. My challenge (perhaps your childs play) was
to create a conditional additional question in a form.

Example:

Why did the chicken cross the road? "Dropdown field containing
multiple choices"

If the selection made above was "To get the other side" Then display a
follow-up question:

Did she say why she wanted to cross the road: "Dropdown field
containing yes and no"

I created the first question and dropdown field using "Question" as
the bookmark name.

follow-up question and dropdown field would be the easiest approach.

I created the follow-up question and dropdown field using "FUA" as the
bookmark name. I then stored the follow-up question and formfield as
AutoText "FUQ."

I used a bookmark "FUQ" to designate the placement of the conditional
question.

Since the FUQ needed to be displayed or hidden depending on the
condition, I realized that I would need to write either the AutoText
FUQ or "" to the bookmark range and redefine the range with each
change.

My first coding attempt was:

Sub OnExitDropDown()
Dim oFF As FormFields
Dim oRng As Word.Range
Set oFF = ActiveDocument.FormFields
Select Case
oFF("Question").DropDown.ListEntries(oFF("Question").DropDown.Value).Name
Case Is = "Yes"

Shouldn't this be:

Case Is "To get the other side"

you did write:

If the selection made above was "To get the other side" Then display a
follow-up question:

I am not sure I understand all the problems you had, but, it seems your
original intent was to display or hide some text (that would include a form)
based on a selection made in a dropdown formfield.

Your approach was to use AutoText.

Right?

If that is the case, I had no problem with the following simple code:

'_______________________________________
Const strBMK_Name As String = "FUQ"
Const strAT_Name As String = "FUQ"
Dim oFF As FormFields
Dim oRng As Word.Range

With ActiveDocument
Set oFF = .FormFields
.Unprotect
Select Case oFF("Question").DropDown.Value
Case 2
Set oRng = .Bookmarks(strBMK_Name).Range
With oRng
'if oRng has more than one charcter,
'the autotext has already been inserted
If Not Len(.Text) > 1 Then
.Text = strAT_Name
.InsertAutoText
End If
End With
Case Else
Set oRng = .Bookmarks(strBMK_Name).Range
With oRng
.Text = ""
End With
End Select
.Bookmarks.Add strBMK_Name, oRng
.Protect wdAllowOnlyFormFields, True
End With
'(The second entry in the main drop down is "To get the other side")
'_______________________________________

But I have probably missed your point....

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

 

[Greg Maxey]

Jean-Guy,

BRAVO!! You nailed the point and I never thought of the
..InsertAutoText method. It appears to work perfectly and as I would
have expected. I am still perplexed with the oddities that I observed
in trying to get to this point and the one that Jay uncovered. There
is certainly something weird about the range object, bookmarks, and
AutoText all trying to play together. Thanks.

I ended up using Select Case ff("Question").Result rather than the
dropdown value method I posted earlier and

Case Is throws and error
Case Is = does not

 

[Greg Maxey]

Jean-Guy,

Actually using your method you don't really need the If Not Len(.Text)

 

[Greg Maxey]

Jay,

If I step through your code and stop after this line:

oRng = oTmpl.AutoTextEntries("FUQ").Insert(where:=oRng, RichText:=True


I can go into the document and use Undo to see that two actions have
taken place. The first is Insert Auto Text which places the functional
dropdown at the proper location. The second is VBA-Range.Text which
puts the non-functional AT at the bookmark range and pushes the working
AT to the right.

I don't understand it, but Jean-Guy's method appears to be the intended
method.

Can you explain the issue with "Command Failed" when I tried to close a
document?

Thanks.

 

[Jean-Guy Marcil]

Greg Maxey was telling us:
Greg Maxey nous racontait que :

Jean-Guy,

Actually using your method you don't really need the If Not Len(.Text)

Actually, you do...

If you do not use this, when ever you tab out of the first question field
with the target answer selected, the conditional question would be replaced.
If the user had already answered and was just tabbing through the fields for
some reason, he would find it odd that the conditional question's answer had
changed...

Just trying to be thorough!

Right?

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

 

[Jean-Guy Marcil]

Greg Maxey was telling us:
Greg Maxey nous racontait que :

Jean-Guy,

BRAVO!! You nailed the point and I never thought of the
.InsertAutoText method. It appears to work perfectly and as I would
have expected. I am still perplexed with the oddities that I observed
in trying to get to this point and the one that Jay uncovered. There
is certainly something weird about the range object, bookmarks, and
AutoText all trying to play together. Thanks.

I ended up using Select Case ff("Question").Result rather than the
dropdown value method I posted earlier and

Case Is throws and error
Case Is = does not

Right, I meant

Case Is = "To get the other side"

But, personally, I never use "Is":

Case "To get the other side"

or, for multiple cases:

Case 2, 3, 5

etc.

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

 

[Greg Maxey]

Yes I see now. Old habit I guess.

Thanks.

Greg Maxey was telling us:
Greg Maxey nous racontait que :


Right, I meant

Case Is = "To get the other side"

But, personally, I never use "Is":

Case "To get the other side"

or, for multiple cases:

Case 2, 3, 5

etc.

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

 

[Greg Maxey]

And being thorough you are!

Greg Maxey was telling us:
Greg Maxey nous racontait que :


Actually, you do...

If you do not use this, when ever you tab out of the first question field
with the target answer selected, the conditional question would be replaced.
If the user had already answered and was just tabbing through the fields for
some reason, he would find it odd that the conditional question's answer had
changed...

Just trying to be thorough!

Right?

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

 

[Jay Freedman]

Hi Greg,

Can you explain the issue with "Command Failed" when I tried to close
a document?

I can't really "explain" it in terms of what causes it, but I've seen
something similar. In this case, I was trying to copy a table from an
existing document to a new document, and then I wanted to close the existing
document without saving. I got the same error. It seems that there was a
handle or something pointing to the table's original location, and that was
preventing the document from closing. The only information I could find
about it was a KB article about PowerPoint 2002, saying there was a hotfix
for that program. :-(

What I eventually wound up with was an OnTime call that fires another macro
1 second later, and that closes the document.

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

 

[Tony Jollans]

I haven't checked this but I would say that the problem is the line:

oRng = oTmpl.AutoTextEntries("FUQ") _
.Insert(where:=oRng, RichText:=True)

which should be:


SET oRng = oTmpl.AutoTextEntries("FUQ") _
.Insert(where:=oRng, RichText:=True)

The Autotext is inserted after (and outside) the bookmark. The Insert method
returns a Range and the default property of the range (the Text) is assigned
to oRng.text which is inside the bookmark. The reason the 'dropdown' doesn't
work is the same as the reason Greg's original failed - you can't insert it
as text.

 

[Jay Freedman]

Thank you, Tony! You're absolutely correct.

I have a bad history of forgetting the Set keyword, and my opinion of the
concept of default properties would blister the phosphors off my screen. It
doesn't help that my primary language at work these days is C#, which
doesn't have either.

 

[Tony Jollans]

my opinion of the

concept of default properties would blister the phosphors off my screen.

LOL! I agree - they cause more problems than they cure.

 

[Greg Maxey]

Yes Tony "Set" or rather the lack of it seemed to be the problem.

Thanks!