Create error msg based on formfield calculation result using VBA

[KimsMisc]

I have a protected Word 03 form with a 2 column table in it (A1:B8). In the table are 3 manual entry textbox fields that you can enter dollar amounts into (B1, B2 & B3), and the remaining 5 fields (B4, B5, B6, B7 & B8) have formula calculations. B4 is the sum of B2+B3. B5 is a percentage ratio of B1/B4.

Basically what I'm trying to do is pop up an error message when B4 (the sum of B2+B3) is less than the dollar amount entered into B1. Or another way of writing it is if B5 is greater than 100%, I need the error message to pop up. Sometimes my code works, sometimes not, and I've written many different ways, spending many different days (& nights), but to no avail. I've tried declaring the variables as currency, integer & string, but I'm just missing something with my programming inexperience. Any help would be greatly appreciated!

Sub Main()
Dim oFld As FormFields
Dim strNum As String
Set oFld = ActiveDocument.FormFields
strNum = oFld("Text36").Result
strNum = oFld("Text39").Result

If oFld ("Text36").Result < oFld("Text39").Result Then
MsgBox "CELL B1 EXCEEDS CELL B4" & vbCrLf & "Use Alternate Calculation"
End If

End Sub

 

[PHV]

I'd try this:
If Val(oFld("Text36").Result) < Val(oFld("Text39").Result) Then

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

 

[KimsMisc]

Thanks for your response PH. I had the sign facing the wrong way in my initial post so that it should read when text36 is GREATER than text39, then display msg:

If Val(oFld("Text36").Result) > Val(oFld("Text39").Result) Then...and this works in every instance except for one, which is what's causing my frustration and confusion.

If the dollar amount entered into Text36 is >= $1,000,000 and the value calculated in Text39 is <= $999,999, the code won't work! I've tested a plethora of different lengths of numbers, but only this one instance continues to not work (which of course will be the most common). Here's a visual:

B1: $2,000,000.00 (Text36)
B2: $ 998,998.00
B3: $ 2.00
B4: $1,000,000.00 (Text39) - code works.

B1: $2,000,000.00 (Text36)
B2: $ 999,998.00
B3: $ 1.00
B4: $ 999,999.00 (Text39) - code will not work.

There's something about Text36 having 7 integers, and Text39 having 6 integers, but it's beyond me.

Thank you again!

 

[TonyJollans]

There are too many unknowns (particularly number formats) to be sure, but your problem is probably that you are doing a text comparison rather than a numeric one - make sure you are using numbers.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website