Forms with Macro Run on Exit

[Mary Sasiela]

I'm creating a simple Word template with form fields
(Forms toolbar) in tables. This is not a VBA form.

I have drop-down fields. My question is one of syntax, I
think. I want to have an IF test based on the selection
in the dropdown on exiting the field (Run Macro on Exit).

So ... if the user selects Yes rather than No, they are
taken to a bookmarked location & forced to enter a comment
(using a DO statement here).

What I'm struggling with is the syntax of the IF line
itself. I've never tested for a form field result.

Would it be - IF FORMDROPDOWN (what you see when you
ALT+f9) = "Yes" then ...
- or -
Would I refer to the Dropdown's bookmark name (in this
case "q1")in the DropDown Form Field Options box?

Once I've got this statement, I can take it from here.

Thx. in advance.

 

[Jay Freedman]

Hi, Mary,

The syntax is a bit more complicated than that (most people would say
it's unnecessarily complicated...). First, to refer to the dropdown at
all, you use the bookmark name from the properties dialog ("q1") to
pick out the proper member of the FormFields collection in the
ActiveDocument. Then, the .Value property of the dropdown tells you
the number -- not the visible text -- of the selected item. If you
want to compare the visible text, you use the .Value to choose from
the .ListEntries collection and get the .Name property of that entry.
(Whew!) So the code would look something like this:

Sub q1_Exit()
Dim answer As String
On Error GoTo Bye
With ActiveDocument.FormFields("q1").DropDown
answer = .ListEntries(.Value).Name
End With
If answer = "Yes" Then
MsgBox "Do 'yes' stuff"
Else
MsgBox "Do 'no' stuff"
End If
Bye:
End Sub

Since you have only the two values "Yes" and "No" to choose from, a
better design is to use a checkbox instead of a dropdown. The code for
the exit macro in that case would look something like this:

Sub Check1_Exit()
On Error GoTo Bye
With ActiveDocument.FormFields("Check1").CheckBox
If .Value = True Then
MsgBox "Do 'yes' stuff"
Else
MsgBox "Do 'no' stuff"
End If
End With
Bye:
End Sub

 

[Guest]

Thanks you Jay! Why did I have a gut feeling it WOULD be
more complicated?

I'll try this today. The drop-down is required because
there could be 3 items - also an "unknown" potentially.
It's only on the YES when there must be an action.

Many thanks again - I'll try it & let you know.

Mary

 

[Mary Sasiela]

Hi Jay - well, conveniently it works if I "keep it
simple". Your sample below works but I need more.

This pasted section of the code below is supposed to, upon
YeS Com, go to a bookmark called c1, then bring up an
Input box requiring a response. If the user types
nothing, it presents itself again, demanding a response.
Else, if any other response in the drop-down list is
selected, no response is required.

Am I not doing this correctly? I'm getting error "Compile
Error - Else without If". But they all match.

"

PASTED code:
Sub YesCom_Exit()
Dim answer As String
On Error GoTo Bye
With ActiveDocument.FormFields("q1").DropDown
answer = .ListEntries(.Value).Name
End With
If answer = "Yes Com" Then
Selection.GoTo What:=wdGoToBookmark, Name:="c1"
Selection.Find.ClearFormatting
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
strComment = InputBox("You selected YES. You are
REQUIRED to enter a comment")
Do
If strComment = "" Then
strComment = InputBox("This is a REQUIRED
response. Type your comment")
Else
Exit Do
End If
Else
Selection.TypeText Text:="do no stuff"
End If
Bye:
End Sub

Thx. in advance if you can help.

Mary
-----Original Message-----

 

[Jay Freedman]

Hi, Mary,

As you gain experience with programming (in any language, not just
VBA), you'll discover that error messages are often way off the mark
-- they tell you that something is wrong, but the thing they suggest
isn't the thing causing the error. :-\

In this, case, the error is that the Do statement must have a matching
Loop statement, like this:

Do
If strComment = "" Then
strComment = InputBox("This is a REQUIRED response")
Else
Exit Do
End If
Loop

VBA knows that it isn't valid to have an Else statement following the
End If, but it doesn't really know why. So it gives you what it thinks
is the "closest" error message.

Once you get this working, you have a lot more to fix up. For one
thing, the whole Selection.Find area should simply be deleted. I
assume you got this by recording the GoTo, but it's completely
unnecessary. Because it doesn't contain a Selection.Find.Execute, it
never actually tries to find anything, which is good because it won't
find an empty string...

The other thing is that if this is a protected form (I assume it must
be because of the dropdown), the Selection.GoTo won't work unless "c1"
is the bookmark name of a text form field. But if it is a text form
field, then a Selection.TypeText statement won't work there. Instead,
eliminate the GoTo, and add this after the new Loop statement:

ActiveDocument.FormFields("c1").Result = strComment