Macro generates runtime error

[QDE]

For ages I have been using a protected form that I created in Word 2000 which
uses a simple macro to add numbers entered in form fields, update a total,
and display the total in a form field. After the upgrade to Word 2003 every
field involved in the macro causes a VB runtime error. I've changed to "low"
security and the macro still won't run on computers that have been upgraded
to 2003. Any suggestions?

 

[Jay Freedman]

Insufficient information. Please post the code of the macro, and the
exact wording of the error message. I can't promise that a diagnosis
will be possible with that information, but it's impossible without
it.

In the meantime, check that every field named in the macro actually
exists with exactly the same name in the form.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[QDE]

Here's the error message: "Run-time error '6124': You are not allowed to
edit this region because document protection is in effect." The document is
protected only for forms and the fields are all form fields that allow data
entry. Input is accepted in all fields except those involved in the macro,
which still runs without errors in all earlier versions of Word.

Here's the macro code:

Sub TableFormula()
' Calculate and display totals for Table 5.
Dim cLabor1Cell As Cell
Dim cLabor2Cell As Cell
Dim cBurden1Cell As Cell
Dim cBurden2Cell As Cell
Dim cMaterial1Cell As Cell
Dim cMaterial2Cell As Cell
Dim cTotal1Cell As Cell
Dim cTotal2Cell As Cell
Dim cCell1Total As Currency
Dim cCell2Total As Currency
' Set variables equal to specified cells.
Set cLabor1Cell = ActiveDocument.Tables(5).Cell(Row:=5, Column:=2)
Set cLabor2Cell = ActiveDocument.Tables(5).Cell(Row:=5, Column:=4)
Set cBurden1Cell = ActiveDocument.Tables(5).Cell(Row:=6, Column:=2)
Set cBurden2Cell = ActiveDocument.Tables(5).Cell(Row:=6, Column:=4)
Set cMaterial1Cell = ActiveDocument.Tables(5).Cell(Row:=7, Column:=2)
Set cMaterial2Cell = ActiveDocument.Tables(5).Cell(Row:=7, Column:=4)
Set cTotal1Cell = ActiveDocument.Tables(5).Cell(Row:=8, Column:=2)
Set cTotal2Cell = ActiveDocument.Tables(5).Cell(Row:=8, Column:=4)
' Calculate totals
cCell1Total = Val(cLabor1Cell.Range.Text) + Val(cBurden1Cell.Range.Text)
+ Val(cMaterial1Cell.Range.Text)
cCell2Total = Val(cLabor2Cell.Range.Text) + Val(cBurden2Cell.Range.Text)
+ Val(cMaterial2Cell.Range.Text)
' Insert results of calculations into cells C8 and E8 of Table 5.
If cCell1Total > 0 Then
cTotal1Cell.Range.Text = Format(cCell1Total, "######.00")
Else
cTotal1Cell.Range.Text = ""
End If
If cCell2Total > 0 Then
cTotal2Cell.Range.Text = Format(cCell2Total, "######.00")
Else
cTotal2Cell.Range.Text = ""
End If
End Sub

 

[Charles Kenyon]

No wonder you are getting error messages. You are adding to tables. Your
code is not set to change any values of form fields. You can't do this while
the document is protected.

What you are talking about is what Word calls an "online form." For more
about online forms, follow the links at
http://addbalance.com/word/wordwebresources.htm#Forms or
http://word.mvps.org/FAQs/Customization/FillinTheBlanks.htm especially Dian
Chapman's series of articles. I strongly recommend that you work through all
of Dian Chapman's articles. They give examples of manipulating online forms
with code.

Hope this helps,
--

Charles Kenyon

Word New User FAQ & Web Directory: http://addbalance.com/word

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide) http://addbalance.com/usersguide




--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

 

[QDE]

Most of the protected form consists of text form fields in tables, which
accept data entry with no problem, except for those involved in the macro,
which worked perfectly until the upgrade to Word 2003. Nothing has changed
except Word. I feel like I must be overlooking some option or something that
will allow this code to continue working as it always has in the past.

Thanks for the links. I'll check them out.

 

[Jay Freedman]

To make the macro work with the least change, use Edit > Replace (in
the VBA editor, not in the main Word document) to replace all
occurrences of

.Text

with

.FormFields(1).Result

This works in both Word 2003 and Word 2000.

In fact, when I tried your original macro in Word 2000, it failed even
earlier than the same macro in Word 2003. In Word 2000 the macro
refused to read the values of the fields in rows 5, 6, and 7; in Word
2003 it would read those values, but then refused to set the totals in
row 8.

The problem is that you're trying to read and write the contents of
the *cells*, not the *form fields in the cells*. In a protected
document, only the contents of the form fields is accessible to VBA,
just as in the user interface.

An all-around better method would be to go into the Properties dialog
of each form field and set its "bookmark name" to an appropriate name.
For example, name them Labor1, Labor 2, Burden1, etc. Then, instead of
declaring and manipulating cells, do all the work directly with the
form fields. The macro would look something like this:

Sub TableFormula()
' Calculate and display totals for Table 5.
Dim Labor1 As FormField
Dim Labor2 As FormField
Dim Burden1 As FormField
Dim Burden2 As FormField
Dim Material1 As FormField
Dim Material2 As FormField
Dim Total1 As FormField
Dim Total2 As FormField
Dim cCell1Total As Currency
Dim cCell2Total As Currency
' Set variables equal to specified cells.
Set Labor1 = ActiveDocument.FormFields("Labor1")
Set Labor2 = ActiveDocument.FormFields("Labor2")
Set Burden1 = ActiveDocument.FormFields("Burden1")
Set Burden2 = ActiveDocument.FormFields("Burden2")
Set Material1 = ActiveDocument.FormFields("Material1")
Set Material2 = ActiveDocument.FormFields("Material2")
Set Total1 = ActiveDocument.FormFields("Total1")
Set Total2 = ActiveDocument.FormFields("Total2")
' Calculate totals
cCell1Total = Val(Labor1.Result) + Val(Burden1.Result) +
Val(Material1.Result)
cCell2Total = Val(Labor2.Result) + Val(Burden2.Result) +
Val(Material2.Result)
' Insert results of calculations into cells C8 and E8 of Table 5.
If cCell1Total > 0 Then
Total1.Result = Format(cCell1Total, "######.00")
Else
Total1.Result = ""
End If
If cCell2Total > 0 Then
Total2.Result = Format(cCell2Total, "######.00")
Else
Total2.Result = ""
End If
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[QDE]

I'll give it a shot. Don't give up on me. I have to wait until I get back
to the office computer, as that is the one with Word 2003. (I must have been
wrong about the previous version being W2000. I just know that it worked
before and now it doesn't!)

 

[QDE]

You're my hero! Your "all-around better method" works lilke a charm! Many
thanks!

Sharon