Calculate a dropdown form field?

[Steve Finlayson]

It appears that you cannot calculate a form field if it is from a
dropdown selection. I am just wanting to confirm this or see if there
is a work around that I cannot see.

I have a table with 6 columns from which I want to select a number,
1-5. Once selected, I want to calculate the average of the column. It
appears that this works only if the data is entered in a numerical
field but not in a field with a dropdown form field.
Please comment.
Thanks
Steve F

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Steve,

The following code in a macro run on exit from the dropdown formfield will
calculate the average of the numbers entered into the four textboxes Text1
thru Text4 and the number selected in the DropDown1 and display the result
in the textbox Text5:

Dim i As Integer, mysum As Long
mysum = 0
For i = 1 To 4
mysum = mysum + Val(ActiveDocument.FormFields("text" & i).Result)
Next i
mysum = mysum + Val(ActiveDocument.FormFields("DropDown1").DropDown.Value)
ActiveDocument.FormFields("Text5").Result = mysum / 5

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Steve Finlayson]

Thanks, Doug
This is encouraging. More specifics, the table has over 30 rows and
some may have data and some will not. Consequently I cannot simply
divide by the total number of rows. Can I use the Formula method to
have the cell accept the formula Formula:="=Average(Above)" (found in
the help example) along with the DropDown.Value feature you used? I do
not know enough VB to put this all together. Can you help. I have 6
such columns but I think that I can adjust what I get from you to
work.
I also am assuming that I need only to call the macro in the Form
Properties box on the on enter box.

Thanks again for the help
Steve

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Steve,

Do something like this:

Dim i As Integer, mysum As Long, Counter as Integer
mysum = 0
Counter = 0
For i = 1 To 4
mysum = mysum + Val(ActiveDocument.FormFields("text" & i).Result)
If Val(ActiveDocument.FormFields("text" & i).Result) <> 0 then
Counter = Counter + 1
End if
Next i
mysum = mysum + Val(ActiveDocument.FormFields("DropDown1").DropDown.Value)
ActiveDocument.FormFields("Text5").Result = mysum / Counter

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Steve Finlayson]

Doug,
Your solution works, though it is not especially fast as it loops
through the 30+ values in each of 6 columns. But I can live with that.
What is the statement that turns off the "echo" so the screen does not
redraw with each loop. I think it would be faster and at least less
annoying if it smoothly went through the loops and then displayed the
results at the end.

Thank you for your help. This is the last piece to this project and it
will be a nice touch to it. I appreciate the help.
Steve