Calculating off combobox selections

  • Thread starter Thread starter Lenny
  • Start date Start date
L

Lenny

My client wants a document that will allow for the selection of the following:
qty of 3 ea (col 1) ceramic mugs from combobox dropdown (col 2) that has a
value of $2.00 (col 3) for a total value of $6.00 (col 4)

I am setting up a shipping document (Word 2003 template w/form fields) where:
col 1 col 2 col 3 col 4
=qty =description =unit value =total value

I have the combobox(s) set up and the total value formulas... I just can't
figure what I need to do with the middle..... any suggestions?

Thanks, Lenny
 
You cannot calculate text items like $2.00. You would need to lose the
currency symbol before you can multiply it by the item in the quantity
field. Then all you need is to check the calculate on exit check box of the
unit value field and in the total value column enter a formula field eg

{ =({ Text1 } * { Dropdown1 }) \#"$,0.00" }

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
Graham: my thanks for the reply, however, sorry I was not clear. I
understand that I have to get rid of the $ sign, my intent was to show a
value in dollars.... where I wanted to go was:
When the user makes a selection from any of the items in the dropdown
(combobox).. I need to have the code automatically add the 'unit value' into
the appropriate cell in order to do the final calculation. In the scenario
below, when the user enters 3 into the qty field, tabs to the combobox and
selects ceramic mug, I need the next column to automatically add the value
2.00 and the final field adds everything in the row. I don't know what vb
function to use to have the unit value field add the appropriate value.....
example: mug = 2.00, shirt = 5.00, cd = 1.00 etc.....
 
That is exactly what the field construction I posted earlier achieves?
{ =({ Text1 } * { Dropdown1 }) \#"$,0.00" }
This assumes the Text1 form field contains the quantity and Dropdown1
contains the unit price. The field multiplies one by the other and produces
a result in dollars?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
Graham: It's not the field construction that I'm having a problem with...let
me have a go at it again.

I have four fields: Quantity, Description, Unit Value and Total.

Quantity is Text1 and a numeric value is entered

Dropdown1 is a Combobox1 loaded with selections the user can make from the
dropdown.

Unit Value is populated from an exit macro in Combobox1 (user does not enter)

Total Value is computer from Quantity * Unit Value

When the user makes the selection from Combobox1, closes the box and tabs,
the exit macro calls up a SelectCase module which 'automatically' populates a
price in the Unit Value column which is then totaled....after a lot of
struggling, I believe I have it mostly figured out. Five of the rows seem to
be working, but the last is not pulling the Unit Value from the SelectCase
and I don't know why.

My question now is once an item is selected from the Combobox, is there a
way to clear the contents of the field so it shows blank? Is there a way to
tie this in to the Select Case in the Unit Value column so the price is
cleared at the same time? Best regards Lenny
 
Your original post said that this was a Word 2003 document with form fields.
There is no 'combo box' form field in Word 2003, only a dropdown box from
which to make selections.
If I understand correctly you are then running a macro on exit from the
dropdown form field that calculates a price based on the content of the
dropdown box, and enters this value where? In a form field?
You cannot have a blank entry in a drop down form field, though you can have
a space and set your macro to deal with the space as a zero entry. Thouigh
frankly you don't need a macro to work out a sub total as I have already
indicated twice.
Your lastest addition to the thread suggests you are tring to combine items
from the control toolbox into a Word form. This isn't going to work. Control
toolbox tools are not compatible with protected Word forms.
Either set the document up to use Word form fields, or abandon the use of
form fields altogether and create the entries from a vba Userform. See Word
MVP FAQ - Userforms http://word.mvps.org/FAQs/Userforms.htm and some of the
uses of such forms at http://gregmaxey.mvps.org/word_tips.htm
vba userform questions have their own forum.
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
Graham: my thanks for your taking the time to respond. I really appreciate
your assistance, but your responses keep leading me to the 'calculation'.
I've already explained that this is not the problem. I get it.

The issue has always been in populating field B with a number based on the
selection from the dropdown combobox list in field A. That's it! My
apologies for not knowing the proper terminology to get across what I'm
trying to accomplish. All I know at this point is that it IS a Word form
WITH form fields. I am NOT using any of the items from the Control Toolbox.

From within form field "A", I reference both an entry macro, (a dropdown
combobox) and an exit macro (referencing a selectcase array which places a
'number') in field 'B' based on the selection from the dropdown combobox. I
have the code working now (mostly) through persistence and a lot of
frustration. The combobox list drops all items into field 'A', but only the
first 5 items in the Select Case array get loaded into field 'B'.... am going
crazy trying to figure it out!

Regards
 
Perhaps it would get us somewhere if you posted the problematic macro?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
Back
Top