Create error msg based on calculation result in formfield using VBA

  • Thread starter Thread starter Kimberley Fudurich
  • Start date Start date
K

Kimberley Fudurich

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
 
Leave B1, B2 and B3 as Regular Text type formfields and put any required
Currency Symbol as ordinary text to the left of the formfield. Then run the
following on exit for B3

With ActiveDocument
If IsNumeric(.FormFields("B1").Result) And
IsNumeric(.FormFields("B2").Result) And IsNumeric(.FormFields("B3").Result)
Then
If Val(.FormFields("B1").Result) > Val(.FormFields("B2").Result) +
Val(.FormFields("B3").Result) Then
MsgBox "CELL B1 EXCEEDS CELL B4" & vbCrLf & "Use Alternate
Calculation"
End If
Else
MsgBox "All Entries in Cells B1, B2 and B3 must be numeric"
End If
End With


--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.
 
Back
Top