summing a column of values from dropdown boxes

[bztips]

Word 2000: I have a table with two form field dropdown boxes in a
column where the user selects a single value from the dropdown box in
each. I want to show the resulting sum total for the two boxes in the
bottom cell of the column.

I've tried inserting a table formula (=b1+b2) -- doesn't work; it's
always equal to zero even though I have checked the "Calculate on exit"
box in b1 and b2 properties.

I've tried writing a macro:
ActiveDocument.FormFields("Coltotal").Result =
ActiveDocument.FormFields("Dropdown1").Value +
ActiveDocument.FormFields("Dropdown2").Value
This also doesn't work even though I've set "Run macro on exit" in b1
and b2. Here I'm also not sure how to properly define the Coltotal
field -- what type of field is it? Dropdown doesn't seem right, how do
I create the appropriate field type in the table cell?

Would the answer be any different if the dropdown boxes and result box
were not inside a table?

Any help appreciated. Thx.

/Bill

 

[Jay Freedman]

The macro is the right idea, but you need to fix the syntax:

ActiveDocument.FormFields("ColTotal").Result = _
Val(ActiveDocument.FormFields("Dropdown1").Result) + _
Val(ActiveDocument.FormFields("Dropdown2").Result)

Note that there's a space before each underscore at the ends of the
first two lines. That's required.

For the ColTotal field, use a text form field. In its Properties
dialog, uncheck the box for "Fill-in enabled" to prevent users from
typing in a number different from the sum of the dropdowns.

It makes no difference to the macro whether the fields are in a table
or in plain text. But the table, if autofit is turned off, will
prevent other text around the form fields from moving when the amount
of text in the fields changes.

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

 

[bztips]

Thanks Jay, that helps alot.

One last problem: I really can't use use a text form field because the
document is going to be mail-merged to email. Mail-merging causes all
textform fields to be lost. (I know there's a workaround on the MS
support site, but it actually only works when you're mail-merging to a
new document, not to email.) Maybe I could define a bookmark where I
want the result, and then have the macro move to the designated spot to
insert the result. I'm not too familiar with how bookmarks work,
though. Any ideas on how this would be written in a macro? Thx.

/Bill

 

[Jay Freedman]

You can use a bookmark to insert the result, but there's a twist...
Because the form is protected, you can't simply insert text at a
bookmark. If you tried, you'd get an error message, "You are not
allowed to edit this region because document protection is in effect."

So the macro has to unprotect the form, insert the text, and reprotect
the form:

Dim Rg As Range

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

Set Rg = .Bookmarks("ColTotal").Range
Rg.Text = _
Val(.FormFields("Dropdown1").Result) + _
Val(.FormFields("Dropdown2").Result)
.Bookmarks.Add Name:="ColTotal", Range:=Rg

.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End With

For an explanation of why I had to re-add the bookmark, see
http://www.word.mvps.org/FAQs/MacrosVBA/InsertingTextAtBookmark.htm.

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

 

[bztips]

Excellent. Thanks again, Jay, I've really learned alot from this.

 

[Jay Freedman]

You're certainly welcome! Glad I could help.

Excellent. Thanks again, Jay, I've really learned alot from this.