Formula to calculate sum based on drop down form fields in Word

I am using Word 2003 and am creating a performance review form. I have 13
drop down fields on all of which the choices can only be 1, 2, 3, 4 or 5. On
the final blank I would like to write a formula which calculates the total of
all the rankings listed.

I have tried EVERYTHING! =SUM(above), =SUM(b2:b14),
=SUM(dropdown1:dropdown13). The colon returns a syntax error. I have listed
them all out with comma separators with no colon and that returns a 0. And,
of course, I have done with and without Ctrl-F9 brackets. I am now begging
for any help you can give. Thank you.

You might be able to do it with a formula field, but I don't know how. I
would use a macro set to run on exit from each variable dropdown field.
Something like this:

Sub TallyResults()

Dim FF As FormField, DDFresult As Long
DDFresult = 0
For Each FF In ActiveDocument.FormFields
If FF.Type = wdFieldFormDropDown Then
If FF.Result = "Superior" Then
DDFresult = DDFresult + 5
ElseIf FF.Result = "Above Average" Then
DDFresult = DDFresult + 4
ElseIf FF.Result = "Average" Then
DDFresult = DDFresult + 3
ElseIf FF.Result = "Below Average" Then
DDFresult = DDFresult + 2
Else
DDFresult = DDFresult + 1
End If
End If
Next FF
ActiveDocument.FormFields("Result").Result = DDFresult

End Sub



--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

"KLane" wrote in message
...
I am using Word 2003 and am creating a performance review form. I have 13
drop down fields on all of which the choices can only be 1, 2, 3, 4 or 5.
On
the final blank I would like to write a formula which calculates the total
of
all the rankings listed.

I have tried EVERYTHING! =SUM(above), =SUM(b2:b14),
=SUM(dropdown1:dropdown13). The colon returns a syntax error. I have
listed
them all out with comma separators with no colon and that returns a 0.
And,
of course, I have done with and without Ctrl-F9 brackets. I am now
begging
for any help you can give. Thank you.

Greg,

This is a bit simpler

Dim FF as FormField
Dim Score as Long
Score = 0
With ActiveDocument
For each FF in .FormFields
If FF.Type = wdFieldFormDropDown then Score = Score +
FF.DropDown.Value
Next FF
.Formfields("Result").Result=Score
End With

--
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

"Greg Maxey" wrote in message
...
You might be able to do it with a formula field, but I don't know how. I
would use a macro set to run on exit from each variable dropdown field.
Something like this:

Sub TallyResults()

Dim FF As FormField, DDFresult As Long
DDFresult = 0
For Each FF In ActiveDocument.FormFields
If FF.Type = wdFieldFormDropDown Then
If FF.Result = "Superior" Then
DDFresult = DDFresult + 5
ElseIf FF.Result = "Above Average" Then
DDFresult = DDFresult + 4
ElseIf FF.Result = "Average" Then
DDFresult = DDFresult + 3
ElseIf FF.Result = "Below Average" Then
DDFresult = DDFresult + 2
Else
DDFresult = DDFresult + 1
End If
End If
Next FF
ActiveDocument.FormFields("Result").Result = DDFresult

End Sub



--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

"KLane" wrote in message
...
I am using Word 2003 and am creating a performance review form. I have 13
drop down fields on all of which the choices can only be 1, 2, 3, 4 or 5.
On
the final blank I would like to write a formula which calculates the
total of
all the rankings listed.

I have tried EVERYTHING! =SUM(above), =SUM(b2:b14),
=SUM(dropdown1:dropdown13). The colon returns a syntax error. I have
listed
them all out with comma separators with no colon and that returns a 0.
And,
of course, I have done with and without Ctrl-F9 brackets. I am now
begging
for any help you can give. Thank you.

Hi KLane,

A formula field coded as:
{={REF DropDown1}+{REF DropDown2}+{REF DropDown3}+{REF DropDown4}+{REF DropDown5}}
will sum the values from your 5 Dropdown fields.

Note: use Ctrl-F9 to create the field braces (ie '{}').

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"KLane" wrote in message ...
I am using Word 2003 and am creating a performance review form. I have 13
drop down fields on all of which the choices can only be 1, 2, 3, 4 or 5. On
the final blank I would like to write a formula which calculates the total of
all the rankings listed.

I have tried EVERYTHING! =SUM(above), =SUM(b2:b14),
=SUM(dropdown1:dropdown13). The colon returns a syntax error. I have listed
them all out with comma separators with no colon and that returns a 0. And,
of course, I have done with and without Ctrl-F9 brackets. I am now begging
for any help you can give. Thank you.

Doug,

I agree. I only posted an example that I had lying about that had dropdown
values of "Superior," "Excellent," "Good," etc.


--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.


Doug Robbins - Word MVP wrote:
Greg,

This is a bit simpler

Dim FF as FormField
Dim Score as Long
Score = 0
With ActiveDocument
For each FF in .FormFields
If FF.Type = wdFieldFormDropDown then Score = Score +
FF.DropDown.Value
Next FF
.Formfields("Result").Result=Score
End With


"Greg Maxey" wrote in message
...
You might be able to do it with a formula field, but I don't know
how. I would use a macro set to run on exit from each variable
dropdown field. Something like this:

Sub TallyResults()

Dim FF As FormField, DDFresult As Long
DDFresult = 0
For Each FF In ActiveDocument.FormFields
If FF.Type = wdFieldFormDropDown Then
If FF.Result = "Superior" Then
DDFresult = DDFresult + 5
ElseIf FF.Result = "Above Average" Then
DDFresult = DDFresult + 4
ElseIf FF.Result = "Average" Then
DDFresult = DDFresult + 3
ElseIf FF.Result = "Below Average" Then
DDFresult = DDFresult + 2
Else
DDFresult = DDFresult + 1
End If
End If
Next FF
ActiveDocument.FormFields("Result").Result = DDFresult

End Sub



--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

"KLane" wrote in message
...
I am using Word 2003 and am creating a performance review form. I
have 13 drop down fields on all of which the choices can only be 1,
2, 3, 4 or 5. On
the final blank I would like to write a formula which calculates the
total of
all the rankings listed.

I have tried EVERYTHING! =SUM(above), =SUM(b2:b14),
=SUM(dropdown1:dropdown13). The colon returns a syntax error. I
have listed
them all out with comma separators with no colon and that returns a
0. And,
of course, I have done with and without Ctrl-F9 brackets. I am now
begging
for any help you can give. Thank you.

That worked perfectly! Thank you very much.

"macropod" wrote:

Hi KLane,

A formula field coded as:
{={REF DropDown1}+{REF DropDown2}+{REF DropDown3}+{REF DropDown4}+{REF DropDown5}}
will sum the values from your 5 Dropdown fields.

Note: use Ctrl-F9 to create the field braces (ie '{}').

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"KLane" wrote in message ...
I am using Word 2003 and am creating a performance review form. I have 13
drop down fields on all of which the choices can only be 1, 2, 3, 4 or 5. On
the final blank I would like to write a formula which calculates the total of
all the rankings listed.

I have tried EVERYTHING! =SUM(above), =SUM(b2:b14),
=SUM(dropdown1:dropdown13). The colon returns a syntax error. I have listed
them all out with comma separators with no colon and that returns a 0. And,
of course, I have done with and without Ctrl-F9 brackets. I am now begging
for any help you can give. Thank you.

Hi,

This seems to work pretty well in word 2000 until it is used in a large document that has a table of contents. It seems to need some sort of refresh.

I also tried this macro and although it also seems to work, it results in making the pages scroll up & down. This of course would be quite painful for the user.

Here's that code :

Sub AddDropDownResults()

Dim dDown1, dDown2, dDown3, dDown4, dDown5, dDown6, dDown7, As Integer
' Get value of first drop down form field.
dDown1 = Val(ActiveDocument.FormFields("DropDown1").Result)
' Get value of second drop down form field.
dDown2 = Val(ActiveDocument.FormFields("DropDown2").Result)
' Get value of thrid drop down form field.
dDown3 = Val(ActiveDocument.FormFields("DropDown3").Result)
' Get value of fouth drop down form field.
dDown4 = Val(ActiveDocument.FormFields("DropDown4").Result)
' Get value of fifth drop down form field.
dDown5 = Val(ActiveDocument.FormFields("DropDown5").Result)
' Get value of sixth drop down form field.
dDown6 = Val(ActiveDocument.FormFields("DropDown6").Result)
' Get value of seveth drop down form field.
dDown7 = Val(ActiveDocument.FormFields("DropDown7").Result)
' Calculate results and place in Text1 form field
ActiveDocument.FormFields("Text1").Result = Str(dDown1 + dDown2 + dDown3 + dDown4 + dDown5 + dDown6 + dDown7)

End Sub


Any help would be greatly appreciated

Thanks

Pedy

Hi pedro,

You don't need a macro to do the calculation. All you need is a formula field (not a formfield), coded as:
{=DropDown1+DropDown2+DropDown3+DropDown4+DropDown 5+DropDown6+DropDown7}
where the field braces (ie '{ }') are created via Ctrl-F9.

As for the TOC, the best way to update it in a form is to use a macro to temporarily unprotect the form, update the TOC, then
reprotect the form (with noreset = true).

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"pedro gracio" wrote in message ...
Hi,

This seems to work pretty well in word 2000 until it is used in a large document that has a table of contents. It seems to need
some sort of refresh.

I also tried this macro and although it also seems to work, it results in making the pages scroll up & down. This of course would
be quite painful for the user.

Here's that code :

Sub AddDropDownResults()

Dim dDown1, dDown2, dDown3, dDown4, dDown5, dDown6, dDown7, As Integer
' Get value of first drop down form field.
dDown1 = Val(ActiveDocument.FormFields("DropDown1").Result)
' Get value of second drop down form field.
dDown2 = Val(ActiveDocument.FormFields("DropDown2").Result)
' Get value of thrid drop down form field.
dDown3 = Val(ActiveDocument.FormFields("DropDown3").Result)
' Get value of fouth drop down form field.
dDown4 = Val(ActiveDocument.FormFields("DropDown4").Result)
' Get value of fifth drop down form field.
dDown5 = Val(ActiveDocument.FormFields("DropDown5").Result)
' Get value of sixth drop down form field.
dDown6 = Val(ActiveDocument.FormFields("DropDown6").Result)
' Get value of seveth drop down form field.
dDown7 = Val(ActiveDocument.FormFields("DropDown7").Result)
' Calculate results and place in Text1 form field
ActiveDocument.FormFields("Text1").Result = Str(dDown1 + dDown2 + dDown3 + dDown4 + dDown5 + dDown6 + dDown7)

End Sub


Any help would be greatly appreciated

Thanks

Pedy

Hi pedro,

Formula Correction:
{={DropDown1}+{DropDown2}+{DropDown3}+{DropDown4}+ {DropDown5}+{DropDown6}+{DropDown7}}

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"macropod" wrote in message ...
Hi pedro,

You don't need a macro to do the calculation. All you need is a formula field (not a formfield), coded as:
{=DropDown1+DropDown2+DropDown3+DropDown4+DropDown 5+DropDown6+DropDown7}
where the field braces (ie '{ }') are created via Ctrl-F9.

As for the TOC, the best way to update it in a form is to use a macro to temporarily unprotect the form, update the TOC, then
reprotect the form (with noreset = true).

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"pedro gracio" wrote in message ...
Hi,

This seems to work pretty well in word 2000 until it is used in a large document that has a table of contents. It seems to need
some sort of refresh.

I also tried this macro and although it also seems to work, it results in making the pages scroll up & down. This of course would
be quite painful for the user.

Here's that code :

Sub AddDropDownResults()

Dim dDown1, dDown2, dDown3, dDown4, dDown5, dDown6, dDown7, As Integer
' Get value of first drop down form field.
dDown1 = Val(ActiveDocument.FormFields("DropDown1").Result)
' Get value of second drop down form field.
dDown2 = Val(ActiveDocument.FormFields("DropDown2").Result)
' Get value of thrid drop down form field.
dDown3 = Val(ActiveDocument.FormFields("DropDown3").Result)
' Get value of fouth drop down form field.
dDown4 = Val(ActiveDocument.FormFields("DropDown4").Result)
' Get value of fifth drop down form field.
dDown5 = Val(ActiveDocument.FormFields("DropDown5").Result)
' Get value of sixth drop down form field.
dDown6 = Val(ActiveDocument.FormFields("DropDown6").Result)
' Get value of seveth drop down form field.
dDown7 = Val(ActiveDocument.FormFields("DropDown7").Result)
' Calculate results and place in Text1 form field
ActiveDocument.FormFields("Text1").Result = Str(dDown1 + dDown2 + dDown3 + dDown4 + dDown5 + dDown6 + dDown7)

End Sub


Any help would be greatly appreciated

Thanks

Pedy

On Apr 17, 11:18*pm, "macropod" wrote:
Hi pedro,

Formula Correction:
{={DropDown1}+{DropDown2}+{DropDown3}+{DropDown4}+ {DropDown5}+{DropDown6}+{*DropDown7}}

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------



"macropod" wrote in . ..
Hi pedro,

You don't need a macro to do the calculation. All you need is a formula field (not a formfield), coded as:
{=DropDown1+DropDown2+DropDown3+DropDown4+DropDown 5+DropDown6+DropDown7}
where the field braces (ie '{ }') are created via Ctrl-F9.

As for the TOC, the best way to update it in a form is to use a macro to temporarily unprotect the form, update the TOC, then
reprotect the form (with noreset = true).

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"pedro gracio" wrote in ...
Hi,

This seems to work pretty well in word 2000 until it is used in a large document that has a table of contents. It seems to need
some sort of refresh.

I also tried this macro and although it also seems to work, it results in making the pages scroll up & down. This of course would
be quite painful for the user.

Here's that code :

Sub AddDropDownResults()

* * *Dim dDown1, dDown2, dDown3, dDown4, dDown5, dDown6, dDown7, As Integer
* * *' Get value of first drop down form field.
* * *dDown1 = Val(ActiveDocument.FormFields("DropDown1").Result)
* * *' Get value of second drop down form field.
* * *dDown2 = Val(ActiveDocument.FormFields("DropDown2").Result)
* * *' Get value of thrid drop down form field.
* * *dDown3 = Val(ActiveDocument.FormFields("DropDown3").Result)
* * *' Get value of fouth drop down form field.
* * *dDown4 = Val(ActiveDocument.FormFields("DropDown4").Result)
* * *' Get value of fifth drop down form field.
* * *dDown5 = Val(ActiveDocument.FormFields("DropDown5").Result)
* * *' Get value of sixth drop down form field.
* * *dDown6 = Val(ActiveDocument.FormFields("DropDown6").Result)
* * *' Get value of seveth drop down form field.
* * *dDown7 = Val(ActiveDocument.FormFields("DropDown7").Result)
* * *' Calculate results and place in Text1 form field
* * *ActiveDocument.FormFields("Text1").Result = Str(dDown1 + dDown2 + dDown3 + dDown4 + dDown5 + dDown6 + dDown7)

End Sub

Any help would be greatly appreciated

Thanks

Pedy- Hide quoted text -

- Show quoted text -

Hi macropod,

Sorry for re-posting, I did not see your reply...

How would I create a "macro to temporarily unprotect the form" ?

The other thing that I tried and it worked great until I placed the
table in a document with a TOC is just using a regular Text Form Field
type Number with the calculate on exit, and in the last cell I used
the formula "=SUM(d2:d21)". This again woked just fine, it calculated
automatically & all but once there is a TOC it didn't do anything...

pedy