Solved: Simple VBA in Word, Calculate Percentage using Form Fields

[Bob1980]

Hello,

I'm very new to VBA, and I'm exploring ways to use VBA within Word documents. What I'm trying to do is to create a macro that calculates a percentage value based upon some pre-existing form fields. I'm new, and the script below is simple; please don't laugh.

I'm also not sure if I correctly tagged the code.

Option Explicit

Sub Percent()
Dim x As Integer
Dim y As Integer
Dim z As Integer

x = ActiveDocument.FormFields("Text1").Select
y = ActiveDocument.FormFields("Text2").Select
z = (x / y) * 100
ActiveDocument.FormFields("Text4").Select = z

End Sub

Thanks for your time!

[fumei]

Do not use Select. There is no need.

Sub Percent()
Dim x As Long
Dim y As Long
Dim z As Long

x = ActiveDocument.FormFields("Text1").Result
y = ActiveDocument.FormFields("Text2").Result
z = (x / y) * 100
ActiveDocument.FormFields("Text4").Result = z

End Sub Do remember that textbox formfield take text, so you would probably want to do some testing. Obviously

(whatever/rad) * 100

is not good. Look up IsNumeric in Help.

[macropod]

I'd have to wonder why you'd even bother with vba in this case, or even a formfield for the output, since the same result can be achieved by setting the 'calculate on exit' property for the "Text1" & "Text2" formfields and a simple formula field where you want the output to go, coded as:
{=({=Text1}/{=Text2})*100 \# "0.00%"}
Note: The field brace pairs (ie '{ }') for the above example are created in the body of the document via Ctrl-F9 (Cmd-F9 on a Mac) - you can't simply type them or copy & paste them from this message.

[fumei]

That would be better than even using VBA as an OnExit. Or, as the OP does not even appear to be using a VBA OnExit, much better than having to fire an essentially extraneous procedure.

[Bob1980]

Thanks for the help everyone! The f9 function worked great. I also found out that I can set the field to be a number if I go to properties after I create it.

Thanks!
Bob1980

[Bob1980]

Hey guys, I know I marked the problem as solved. But, I found another issue. Whenever I enter new data into the text1 or text2 field, the bookmark disappears, and I get an error message where the percent should be calculated.

[macropod]

That suggests your document doesn't have formfield protection applied.

[fumei]

What bookmark are you talking about? I thought you are dealing with formfields.

[macropod]

Hi Gerry,

Given the previous discussion, I'd assume the formfield bookmarks - Text1 & Text2.

[fumei]

why would:

enter new data into the text1 or text2 field, the bookmark disappears

Is the OP saying the formfield remains...but the bookmark disappears?

[macropod]

If you don't have forms protection 'on', typing where the formfield is deletes it and its bookmark.

[fumei]

Well that makes my fever make sense.