How to reference a field's content using its bookmark name

[Guest]

I am a new user of VBA.

I want to write code that compares the values of two fields. One field's
value is expected to be less than or equal to the other and, if that is not
the case, I want to display a message to the user. The bookmark names for
the fields are 'fundingShortfall' and 'requestAmt'. 'fundingShortfall' is a
cell in a WORD table, whose value is calculated with a formula, using values
from another WORD table in the same document. 'requestAmt' is entered by the
user into a text form field in a cell in the same WORD table as
'fundingShortfall'. I want my code to run immediately after the user enters
the value of 'requestAmt'.

Can someone illustrate how to code the names of the two field's values in an
IF statement in VBA? That is, the full-qualified names and syntax for an IF
using these values based on the bookmark names?

Thanks.

 

[Greg]

Prich,

A lot depends on how you are calculating the fundingShortfall. As you
are obviously using a protected form, I will assume that
fundingShortfall value is determined by a calculation field.

The following set to run on exit from the requestAmt field should work:
Sub Message()
Dim oDoc As Document
Set oDoc = ActiveDocument
If Val(oDoc.FormFields("requestAmt").Result) >
Val(oDoc.FormFields("fundingShortfall").Result) Then
MsgBox "Bob's your uncle."
End If
End Sub

 

[pwrichcreek]

Greg,

I cut and pasted your code and it ran when I exited the requestAmt field,
but it gives a syntax error on this statement:

If Val(oDoc.FormFields("requestAmt").Result) >

I don't know how to find the correct syntax for FormFields. The only thing I
know to check is that "requestAmt" is actually the name of the bookmark, and
it is.

Phil

 

[Doug Robbins]

Either insert a VBA linebreak character

If Val(oDoc.FormFields("requestAmt").Result) > _

or remove the carriage return at the end of that line of code so that it and
the next line are all on one line.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[pwrichcreek]

I am executing this now...

If Val(oDoc.FormFields("requestAmt").Result) > _
Val(oDoc.FormFields("fundingShortfall").Result) Then
MsgBox "Bob's your uncle."
End If

and the syntax error does not occur.

But now I get a run-time error 5941 "The requested member of the collection
does not exist". In the debugger the entire If statement is highlighted in
yellow and there is a yellow arrow pointing at

Val(oDoc.FormFields("fundingShortfall").Result) Then

Indeed, "fundingShortfall" is not a form field. It is a table cell whose
value is the result of adding two other cells in the same table. I did some
research and experimenting and find that the "fundingShortfall" bookmark
EXISTS in the FIELDS collection. When I try to retrieve its content with VAL
or simply by assigning it, as in

Dim iShortfall As Integer
iShortfall = oDoc.Fields("fundingShortfall").Result

I get TYPE errors. I tried assigning to a STRING variable, and also using
VAL with FIELDS instead of FORMFIELDS. I think those gave TYPE errors as
well, but it might have been some other error. I've tried so many different
things I've sortof forgotten some of the details, but bottom line is I've
been unable to get anything to work.

As I stated at the outset, I'm new to VBA, and I'm starting to wonder if
what I'm trying to do is not a good design. Should I be using another
approach? Is it unusual to compare a couple of fields within a WORD document
with a macro?

Thanks,

Phil

 

[Greg]

Phil,

We all where new at one point. I have felt and still fell your pain at
time ;-)

Here is an example of what T 'think" you are trying to do. There is a
table with 1 row and four columns. This is the first table in the
document so we will refer to it by its index value "1". Cell A1
contains a number formfield bookmarked requestAmt with a macor name
"message" set to run on exit. Cells 2 and 3 both contain a number
formfield set to run the macro "message" on exit. Cell 4 holds the
following formulat {=(b1+c1)}.

Here is the code for the macro "message"

Sub Message()
Dim oDoc As Document
Set oDoc = ActiveDocument
oDoc.Fields(4).Update
If Val(oDoc.FormFields("requestAmt").Result) > _
Val(oDoc.Tables(1).Cell(1, 4).Range.Text) Then
MsgBox "Bob's your uncle."
End If
End Sub

You don't really need to bookmark the value in table cell d1 as you can
use the cell reference Cell(1, 4) which means row 1 column 4. If you
really wanted to use a bookmark value then go head and bookmark the
value in cell d4 and use this:

Sub Message()
Dim oDoc As Document
Set oDoc = ActiveDocument
oDoc.Fields(4).Update
If Val(oDoc.FormFields("requestAmt").Result) > _
Val(oDoc.Bookmarks("fundingShortfall").Range.Text) Then
MsgBox "Bob's your uncle."
End If
End Sub

Of course you need to know the field index of the formula field. Here
it the fourth field in a simple document. If you don't know, you can
select the field and run:

Sub FindFieldIndex()
MsgBox Selection.Fields(1).Index
End Sub

 

[Greg]

At least you can spell "where new" ;-(

I don't consider what you are trying to do unusual at all.

 

[pwrichcreek]

Greg,

I went with the bookmark approach and it works fine. Thanks for all your help.

Phil

 

[Greg Maxey]

My pleasure.