Calculating from a dropdown box?

[Angyl]

How do I get a calculation to work from a dropdown box in a Word Form?

I know how to make =(Field1)*(Field2) work properly but what if one of the
fields is a dropdown box? That normal formula isn't working.

 

[Jean-Guy Marcil]

Angyl was telling us:
Angyl nous racontait que :

How do I get a calculation to work from a dropdown box in a Word Form?

I know how to make =(Field1)*(Field2) work properly but what if one
of the fields is a dropdown box? That normal formula isn't working.

You need a macro for that, at least, I do not know of any other way.

In this macro example, the dropdown is named "Increment", the text box is
named "Value" and the text box where the result appears is named "Result".
The "Result" text box is not enabled for user input and the "Value" text box
has its "Calculate on exit" property activated.

Sub GetTotal()

With ActiveDocument.FormFields
If .Item("Value").Result = "" Then
.Item("Result").Result = "0"
Exit Sub
End If
.Item("Result").Result = _
CStr(CSng(.Item("Value").Result) * _
CSng(.Item("Increment").DropDown.ListEntries _
(.Item("Increment").DropDown.Value).Name))
End With

End Sub



--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[macropod]

Hi Angyl,

If you set both Dropdown fields' properties to 'calculate on exit', a formula field coded as:
{={REF Dropdown1}*{REF Dropdown2}}
(where 'Dropdown1' and 'Dropdown2' are the bookmark names set by these fields) will calculate correctly without the need for vba.
You can also add numeric picture switches to the formula field format the output.

Cheers

--
macropod
[MVP - Microsoft Word]


| How do I get a calculation to work from a dropdown box in a Word Form?
|
| I know how to make =(Field1)*(Field2) work properly but what if one of the
| fields is a dropdown box? That normal formula isn't working.

 

[Greg Maxey]

Angyl was telling us:
Angyl nous racontait que :



You need a macro for that, at least, I do not know of any other way.

In this macro example, the dropdown is named "Increment", the text box is
named "Value" and the text box where the result appears is named "Result".
The "Result" text box is not enabled for user input and the "Value" text box
has its "Calculate on exit" property activated.

Sub GetTotal()

With ActiveDocument.FormFields
If .Item("Value").Result = "" Then
.Item("Result").Result = "0"
Exit Sub
End If
.Item("Result").Result = _
CStr(CSng(.Item("Value").Result) * _
CSng(.Item("Increment").DropDown.ListEntries _
(.Item("Increment").DropDown.Value).Name))
End With

End Sub

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site:http://www.word.mvps.org

JGM,

Why all the contortions in the formula? Why not just:
.Item("Result").Result = .Item("Value").Result * _
.Item("Increment").Result

 

[Jean-Guy Marcil]

macropod was telling us:
macropod nous racontait que :

Hi Angyl,

If you set both Dropdown fields' properties to 'calculate on exit', a
formula field coded as: {={REF Dropdown1}*{REF Dropdown2}}
(where 'Dropdown1' and 'Dropdown2' are the bookmark names set by
these fields) will calculate correctly without the need for vba. You
can also add numeric picture switches to the formula field format the
output.

I tested Angyl's formula yesterday (=(Field1)*(Field2) and saw that it did
not work...

Since I have never used dropdown in formulas, I did not really look into
it...

<blush>

I forgot to test with the REF fields...
Duh!

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jean-Guy Marcil]

Greg Maxey was telling us:
Greg Maxey nous racontait que :

Why all the contortions in the formula? Why not just:
.Item("Result").Result = .Item("Value").Result * _
.Item("Increment").Result

Hi Greg,

Why do it the easy way?

I do not know where my head was last night...

I started writing the code with something like:
ActiveDocument.FormFields("Increment").DropDown.Value
which returns the ListIndex number, not the value of the selected item... So
I used ListEntries to get to the actual value...

I totally forgot that if I did not specify that the formfield was a dropdown
one .Result will return the selected item value, not its index number...

It was not my night!

Note that I would still use

.Item("Result").Result = CStr(CSng(.Item("Value").Result) * _
CSng(.Item("Increment").Result))

Just because I do not like the compiler converting my variables for me...

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[macropod]

Hi Jean-Guy,

It's always the simple things ...

Cheers

--
macropod
[MVP - Microsoft Word]


| macropod was telling us:
| macropod nous racontait que :
|
| > Hi Angyl,
| >
| > If you set both Dropdown fields' properties to 'calculate on exit', a
| > formula field coded as: {={REF Dropdown1}*{REF Dropdown2}}
| > (where 'Dropdown1' and 'Dropdown2' are the bookmark names set by
| > these fields) will calculate correctly without the need for vba. You
| > can also add numeric picture switches to the formula field format the
| > output.
|
| I tested Angyl's formula yesterday (=(Field1)*(Field2) and saw that it did
| not work...
|
| Since I have never used dropdown in formulas, I did not really look into
| it...
|
| <blush>
|
| I forgot to test with the REF fields...
| Duh!
|
| --
|
| Salut!
| _______________________________________
| Jean-Guy Marcil - Word MVP
| (e-mail address removed)
| Word MVP site: http://www.word.mvps.org
|
|