Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Protected word XP template - calculate formula macro 1

Status
Not open for further replies.

Danielbryanuk

Technical User
Sep 26, 2007
48
DE
Hi there, I'd be very grateful if anyone can advise on the following issue?

I have created a form template in Word XP, which contains a table with simple formulas referencing other cells in the table. I have added fields to control user input, and have protected to document to restrict editing of anything else.

As with formulas in Word calculation is not automatic. To do this users must select the entire table and press F9, or right click the cell and update field.

However with a protected document users cannot select the "output" field cell to update, nor the entire table. As a work-around I created a macro in the form template as per the below, which is run on exit from a certain "input" field in the table.

Sub Updatetable()

' Updatetable Macro

Selection.WholeStory
Selection.Fields.Update

End Sub

The problem is that this method is not available with a protected document. Any ideas how I can update the table automatically, but keep the form template protected?

Thanks
Dan





 
If you set your user input fields up as "Calculate on exit", that is what should happen without the need for any other code.

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
 
Another option is to add to the macro to unprotect the document, run what you already have and then re-protect the document. I have used this with a print macro in Excel with no real issues.
 
Thanks Tony and Renasant.

Tony, I tried your suggestion originally but it didn't seem to calculate the "result" field. Does the "Calculate on Exit" functionality work only on the field it is applied to, i.e. the "input" field?

Thanks
Dan
 
Hi Dan,

The 'calculate on exit' formfield property applies to all fields that have a direct or indirect dependency on the formfield in question. Setting this property forces an automatic recalc of the affected fields as soon as the formfield in question is exited. If some fields are not updating as expected, there is likely an error in their formulae.


Cheers
[MS MVP - Word]
 
As I understand it, "Calculate on exit" updates all fields. There may be some limit to its scope in some circumstances, but it certainly updates more than just the fields with a dependency on the field being exited.

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
 
Hi Tony,

Yes, you're right - it'll update any formula fields, regardless of dependencies. Strangely, it even tries to update ASK fields, FILLIN fields & the like.


Cheers
[MS MVP - Word]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top