Calculations in Word 1

[Edrondol]

This is similar in scope to what SamDemon wanted to know in thread "thread68-817810".

I have a document that (due to managerial doctrine) MUST be done in Word with calculations. This is a 3 column table that has the following properties:

A1: Dollar Amount (Call this Cell Monthly)
B1: 24
C1: Dollar Amount (Call this Cell Annual)

Cell C1 can not be over $5000 and is essentially A1*24. I had this all set up and working correctly, so that if the amount in C1>5000, then it would revert back to $0.

This was not good enough because manager wants to have an error message. I can not get this to display text.

Specifically what I need is A1=C1/24. If they put more than $5000 in C1, then A1 should state something like "Annual Amount Can not Exceed $5000".

any helping ideas?





-Dave the Perpetually Confused

 

[fumei]

1. You need something to fire a check of the value of C1. The original post had formfields, and if so, then write an OnExit macro for C1 formfield. It can handle a message.

on the other hand....

2. Which way are you calculating? I am not sure if A1 = C1/24...or C1 = A1 * 24. Which is actually data entered?



Gerry

 

[Edrondol]

Calculating backwards. I had it set up right the first time, but he decided I needed to put the amount in C1 so that it calculates A1.





-Dave the Perpetually Confused

 

[macropod]

Hi Edrondol,

In A1, use the following formula:
{IF{C1}> 5000 "Annual Amount Cannot Exceed $5000" {=C1/24}}

Cheers

 

[fumei]

You could use macropod's method, but are you still using formfields? If so, you could also use this as the OnExit from C1

Sub UpdateA1()
If ActiveDocument.FormFields("[i]C1_formfieldName[/i]").Result > 5000 Then
  Msgbox "Annual Amount Can not Exceed $5000"
Else
ActiveDocument.FormFields("[i]A1_formfieldName[/i]").Result = _
ActiveDocument.FormFields("[i]C1_formfieldName[/i]").Result
End If
End Sub

Gerry

 

[macropod]

Hi Fumei,

If using formfields, setting 'calculate on exit' as a formfield field property would achieve the same result as the macro.

Cheers

 

[Edrondol]

Thanks, guys! Worked like a charm. I was putting commas in like it was Excel. Have a star, Macropod!






-Dave the Perpetually Confused