Calculating a value based on a text field

[TDugan]

Hi,

I have a userform in excel with a field called txtBenefitAmt. As part of my code, I have excel launch a word document and populate information given to me in the excel userform. The word document does not have a userform but I have txt boxes on the doc itself.

However, in certain instances I need to take the value input in txtBenefitAmt and manipulate it before inputting the info in the word document. Anything I have tried has not worked.

An example:
User inputs 300 in txtBenefitAmt field in excel. In one scenario of my code, I need to take half of that 300 and input that in the word doc field.

I have tried several things but one of them being:
wdDoc.txtJandSamt.Text = frmRLRP.txtBenefitAmt.Text / 2

It works fine when I don't try to manipulate the txtBenefitAmt value.

Any ideas on how to make this work?

Thank you.

 

[Andrzejek]

So what happens when you DO try to manipulate the txtBenefitAmt value?
Do you get any errors? Does it blow up?

I would be tempted to do:
[tt]
wdDoc.txtJandSamt.Text = Val(frmRLRP.txtBenefitAmt.Text) / 2[/tt]





Have fun.

---- Andy

 

[mintjulep]

The content of the .Text property of a Text box is of the Text data type.

You can't do math with text.

If you have text that looks like a number then you need to Coerce it to an appropriate numeric data type.

 

[TDugan]

Andy,

Your suggestion of "wdDoc.txtJandSamt.Text = Val(frmRLRP.txtBenefitAmt.Text) / 2" did the trick. Thank you very much.

I'd like to ask another question though.

Since I am dealing with dollar figures, I need it to round the result to 2 decimal places.

Currently, if txtBenefitAmt is 346.75...it sets txtJandSamt to 173.375. Is there a way to get it to round to 173.38?

I appreciate your help!!!

Teresa

 

[Andrzejek]

How about:

Dim curMyMoney As Currency

curMyMoney = 346.75 / 2

MsgBox [blue]Round([/blue]curMyMoney[blue], 2)[/blue]

Have fun.

---- Andy

 

[PHV]

Either:
wdDoc.txtJandSamt.Text = Round'Val(frmRLRP.txtBenefitAmt.Text) / 2, 2)
or:
wdDoc.txtJandSamt.Text = Format(Val(frmRLRP.txtBenefitAmt.Text) / 2, "#0.00")


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TDugan]

PHV,
Thank you! Both of your options worked. I liked option 2 better because it forced the decimal places to display in the word doc.

I really appreciate your help.