PLease Help!!!Formulas not updating and macros disappearing

Hello Everyone,

I have a pretty complex form that uses many tables, formfields, macros, etc.
and I have two major problems:
In one of the sections I have a table with number fields and formulas to
calculate.

B4*B5* in one field and B6 =if(B6=0,0,(B7*3)+b3) in another. In fact each
column have the same relative formulas. They are set up as calculated
formfields.....not just formulas in the cell. When first created they seemed
fine, but now they do not update at all. The form is protected as it should
be and I have a macro set at the end of the form to unprotect, spell check
and I assumed update the calculation fields. It just no longer works. I
thought there was a line of code in the macro that selected all of the
document and updated, but it seems to have been taken out somehow. Here is
the code behind the macro courtesy MVPs.

Sub FormsSpellCheck()

' If document is protected, Unprotect it.
If ActiveDocument.ProtectionType wdNoProtection Then
ActiveDocument.Unprotect Password:="not2day"
End If

' Set the language for the document.
Selection.WholeStory
Selection.LanguageID = wdEnglishUS

' Perform Spelling/Grammar check.
If Options.CheckGrammarWithSpelling = True Then
ActiveDocument.CheckGrammar
Else
ActiveDocument.CheckSpelling
End If

' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End If

End Sub

The second problem has to do with the template and all of its macros. This
document of course has been saved as a template. All of the macros are
stored in the template. A co-worker emailed me the template with suggested
changes (Not tracking) and none of the macros were with the template. I have
many macros for doing things like validation and adding new rows as well as
the spell check macro. I do not understand why this is happening and how to
stop it. We need many people to download this template and use it with
macros intact. I have other forms and they were also emailed to me and they
work fine.

Please help!!!!!!

Do you have "Calculate on exit" checked in each form field that contributes
to the calculation?

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

"Kay" wrote in message
...
Hello Everyone,

I have a pretty complex form that uses many tables, formfields, macros,
etc.
and I have two major problems:
In one of the sections I have a table with number fields and formulas to
calculate.

B4*B5* in one field and B6 =if(B6=0,0,(B7*3)+b3) in another. In fact
each
column have the same relative formulas. They are set up as calculated
formfields.....not just formulas in the cell. When first created they
seemed
fine, but now they do not update at all. The form is protected as it
should
be and I have a macro set at the end of the form to unprotect, spell check
and I assumed update the calculation fields. It just no longer works. I
thought there was a line of code in the macro that selected all of the
document and updated, but it seems to have been taken out somehow. Here
is
the code behind the macro courtesy MVPs.

Sub FormsSpellCheck()

' If document is protected, Unprotect it.
If ActiveDocument.ProtectionType wdNoProtection Then
ActiveDocument.Unprotect Password:="not2day"
End If

' Set the language for the document.
Selection.WholeStory
Selection.LanguageID = wdEnglishUS

' Perform Spelling/Grammar check.
If Options.CheckGrammarWithSpelling = True Then
ActiveDocument.CheckGrammar
Else
ActiveDocument.CheckSpelling
End If

' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End If

End Sub

The second problem has to do with the template and all of its macros.
This
document of course has been saved as a template. All of the macros are
stored in the template. A co-worker emailed me the template with
suggested
changes (Not tracking) and none of the macros were with the template. I
have
many macros for doing things like validation and adding new rows as well
as
the spell check macro. I do not understand why this is happening and how
to
stop it. We need many people to download this template and use it with
macros intact. I have other forms and they were also emailed to me and
they
work fine.

Please help!!!!!!

Suzanne,

I do and I have tried the formula several different ways. One is to use a
calculated field with the formula and the other is the table, formula option.
I have tried replacing the cell reference withe the book mark name of the
form field as well. A form has to be protected of course for forms and
sections...so how are you supposed to be able to update it.


"Suzanne S. Barnhill" wrote:

Do you have "Calculate on exit" checked in each form field that contributes
to the calculation?

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

"Kay" wrote in message
...
Hello Everyone,

I have a pretty complex form that uses many tables, formfields, macros,
etc.
and I have two major problems:
In one of the sections I have a table with number fields and formulas to
calculate.

B4*B5* in one field and B6 =if(B6=0,0,(B7*3)+b3) in another. In fact
each
column have the same relative formulas. They are set up as calculated
formfields.....not just formulas in the cell. When first created they
seemed
fine, but now they do not update at all. The form is protected as it
should
be and I have a macro set at the end of the form to unprotect, spell check
and I assumed update the calculation fields. It just no longer works. I
thought there was a line of code in the macro that selected all of the
document and updated, but it seems to have been taken out somehow. Here
is
the code behind the macro courtesy MVPs.

Sub FormsSpellCheck()

' If document is protected, Unprotect it.
If ActiveDocument.ProtectionType wdNoProtection Then
ActiveDocument.Unprotect Password:="not2day"
End If

' Set the language for the document.
Selection.WholeStory
Selection.LanguageID = wdEnglishUS

' Perform Spelling/Grammar check.
If Options.CheckGrammarWithSpelling = True Then
ActiveDocument.CheckGrammar
Else
ActiveDocument.CheckSpelling
End If

' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End If

End Sub

The second problem has to do with the template and all of its macros.
This
document of course has been saved as a template. All of the macros are
stored in the template. A co-worker emailed me the template with
suggested
changes (Not tracking) and none of the macros were with the template. I
have
many macros for doing things like validation and adding new rows as well
as
the spell check macro. I do not understand why this is happening and how
to
stop it. We need many people to download this template and use it with
macros intact. I have other forms and they were also emailed to me and
they
work fine.

Please help!!!!!!

A calculated field should update automatically if all its components are set
to "Calculate on exit." The catch is that "exit" means "tab out." If you
don't tab (but instead just move the mouse), this isn't triggered.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

"Kay" wrote in message
...
Suzanne,

I do and I have tried the formula several different ways. One is to use a
calculated field with the formula and the other is the table, formula
option.
I have tried replacing the cell reference withe the book mark name of the
form field as well. A form has to be protected of course for forms and
sections...so how are you supposed to be able to update it.


"Suzanne S. Barnhill" wrote:

Do you have "Calculate on exit" checked in each form field that
contributes
to the calculation?

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so
all may benefit.

"Kay" wrote in message
...
Hello Everyone,

I have a pretty complex form that uses many tables, formfields,
macros,
etc.
and I have two major problems:
In one of the sections I have a table with number fields and formulas
to
calculate.

B4*B5* in one field and B6 =if(B6=0,0,(B7*3)+b3) in another. In fact
each
column have the same relative formulas. They are set up as calculated
formfields.....not just formulas in the cell. When first created they
seemed
fine, but now they do not update at all. The form is protected as it
should
be and I have a macro set at the end of the form to unprotect, spell
check
and I assumed update the calculation fields. It just no longer works.
I
thought there was a line of code in the macro that selected all of the
document and updated, but it seems to have been taken out somehow.
Here
is
the code behind the macro courtesy MVPs.

Sub FormsSpellCheck()

' If document is protected, Unprotect it.
If ActiveDocument.ProtectionType wdNoProtection Then
ActiveDocument.Unprotect Password:="not2day"
End If

' Set the language for the document.
Selection.WholeStory
Selection.LanguageID = wdEnglishUS

' Perform Spelling/Grammar check.
If Options.CheckGrammarWithSpelling = True Then
ActiveDocument.CheckGrammar
Else
ActiveDocument.CheckSpelling
End If

' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
NoReset:=True
End If

End Sub

The second problem has to do with the template and all of its macros.
This
document of course has been saved as a template. All of the macros
are
stored in the template. A co-worker emailed me the template with
suggested
changes (Not tracking) and none of the macros were with the template.
I
have
many macros for doing things like validation and adding new rows as
well
as
the spell check macro. I do not understand why this is happening and
how
to
stop it. We need many people to download this template and use it
with
macros intact. I have other forms and they were also emailed to me
and
they
work fine.

Please help!!!!!!

Suzanne,

You are certainly correct....but it doesn't do that. I have tried
recreating every field and I even created a macro that will update and that
solves the problem for one row of formulas but not for the other. My macro
moves to the calculated field, selects the whole row, updates and then moves
to the next row and selects that row and updates. The first row updates, but
the second does not...probably because the second row is a calculation that
uses the answer from the first calculation multiplied by 3 and + to another
number field in the same table. I really did not want to use Excel in this
document because it is already complex enough.

"Suzanne S. Barnhill" wrote:

A calculated field should update automatically if all its components are set
to "Calculate on exit." The catch is that "exit" means "tab out." If you
don't tab (but instead just move the mouse), this isn't triggered.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

"Kay" wrote in message
...
Suzanne,

I do and I have tried the formula several different ways. One is to use a
calculated field with the formula and the other is the table, formula
option.
I have tried replacing the cell reference withe the book mark name of the
form field as well. A form has to be protected of course for forms and
sections...so how are you supposed to be able to update it.


"Suzanne S. Barnhill" wrote:

Do you have "Calculate on exit" checked in each form field that
contributes
to the calculation?

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so
all may benefit.

"Kay" wrote in message
...
Hello Everyone,

I have a pretty complex form that uses many tables, formfields,
macros,
etc.
and I have two major problems:
In one of the sections I have a table with number fields and formulas
to
calculate.

B4*B5* in one field and B6 =if(B6=0,0,(B7*3)+b3) in another. In fact
each
column have the same relative formulas. They are set up as calculated
formfields.....not just formulas in the cell. When first created they
seemed
fine, but now they do not update at all. The form is protected as it
should
be and I have a macro set at the end of the form to unprotect, spell
check
and I assumed update the calculation fields. It just no longer works.
I
thought there was a line of code in the macro that selected all of the
document and updated, but it seems to have been taken out somehow.
Here
is
the code behind the macro courtesy MVPs.

Sub FormsSpellCheck()

' If document is protected, Unprotect it.
If ActiveDocument.ProtectionType wdNoProtection Then
ActiveDocument.Unprotect Password:="not2day"
End If

' Set the language for the document.
Selection.WholeStory
Selection.LanguageID = wdEnglishUS

' Perform Spelling/Grammar check.
If Options.CheckGrammarWithSpelling = True Then
ActiveDocument.CheckGrammar
Else
ActiveDocument.CheckSpelling
End If

' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
NoReset:=True
End If

End Sub

The second problem has to do with the template and all of its macros.
This
document of course has been saved as a template. All of the macros
are
stored in the template. A co-worker emailed me the template with
suggested
changes (Not tracking) and none of the macros were with the template.
I
have
many macros for doing things like validation and adding new rows as
well
as
the spell check macro. I do not understand why this is happening and
how
to
stop it. We need many people to download this template and use it
with
macros intact. I have other forms and they were also emailed to me
and
they
work fine.

Please help!!!!!!

You will get errors if you use the result of one FormField calculation as
the input to another calculation.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Kay" wrote in message
news
Suzanne,

You are certainly correct....but it doesn't do that. I have tried
recreating every field and I even created a macro that will update and
that
solves the problem for one row of formulas but not for the other. My
macro
moves to the calculated field, selects the whole row, updates and then
moves
to the next row and selects that row and updates. The first row updates,
but
the second does not...probably because the second row is a calculation
that
uses the answer from the first calculation multiplied by 3 and + to
another
number field in the same table. I really did not want to use Excel in
this
document because it is already complex enough.

"Suzanne S. Barnhill" wrote:

A calculated field should update automatically if all its components are
set
to "Calculate on exit." The catch is that "exit" means "tab out." If you
don't tab (but instead just move the mouse), this isn't triggered.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup
so
all may benefit.

"Kay" wrote in message
...
Suzanne,

I do and I have tried the formula several different ways. One is to
use a
calculated field with the formula and the other is the table, formula
option.
I have tried replacing the cell reference withe the book mark name of
the
form field as well. A form has to be protected of course for forms and
sections...so how are you supposed to be able to update it.


"Suzanne S. Barnhill" wrote:

Do you have "Calculate on exit" checked in each form field that
contributes
to the calculation?

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so
all may benefit.

"Kay" wrote in message
...
Hello Everyone,

I have a pretty complex form that uses many tables, formfields,
macros,
etc.
and I have two major problems:
In one of the sections I have a table with number fields and
formulas
to
calculate.

B4*B5* in one field and B6 =if(B6=0,0,(B7*3)+b3) in another. In
fact
each
column have the same relative formulas. They are set up as
calculated
formfields.....not just formulas in the cell. When first created
they
seemed
fine, but now they do not update at all. The form is protected as
it
should
be and I have a macro set at the end of the form to unprotect,
spell
check
and I assumed update the calculation fields. It just no longer
works.
I
thought there was a line of code in the macro that selected all of
the
document and updated, but it seems to have been taken out somehow.
Here
is
the code behind the macro courtesy MVPs.

Sub FormsSpellCheck()

' If document is protected, Unprotect it.
If ActiveDocument.ProtectionType wdNoProtection Then
ActiveDocument.Unprotect Password:="not2day"
End If

' Set the language for the document.
Selection.WholeStory
Selection.LanguageID = wdEnglishUS

' Perform Spelling/Grammar check.
If Options.CheckGrammarWithSpelling = True Then
ActiveDocument.CheckGrammar
Else
ActiveDocument.CheckSpelling
End If

' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
NoReset:=True
End If

End Sub

The second problem has to do with the template and all of its
macros.
This
document of course has been saved as a template. All of the macros
are
stored in the template. A co-worker emailed me the template with
suggested
changes (Not tracking) and none of the macros were with the
template.
I
have
many macros for doing things like validation and adding new rows as
well
as
the spell check macro. I do not understand why this is happening
and
how
to
stop it. We need many people to download this template and use it
with
macros intact. I have other forms and they were also emailed to me
and
they
work fine.

Please help!!!!!!

Doug,

Would you then recommend using excel, or is there a workaround for this
problem?

"Doug Robbins - Word MVP" wrote:

You will get errors if you use the result of one FormField calculation as
the input to another calculation.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Kay" wrote in message
news
Suzanne,

You are certainly correct....but it doesn't do that. I have tried
recreating every field and I even created a macro that will update and
that
solves the problem for one row of formulas but not for the other. My
macro
moves to the calculated field, selects the whole row, updates and then
moves
to the next row and selects that row and updates. The first row updates,
but
the second does not...probably because the second row is a calculation
that
uses the answer from the first calculation multiplied by 3 and + to
another
number field in the same table. I really did not want to use Excel in
this
document because it is already complex enough.

"Suzanne S. Barnhill" wrote:

A calculated field should update automatically if all its components are
set
to "Calculate on exit." The catch is that "exit" means "tab out." If you
don't tab (but instead just move the mouse), this isn't triggered.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup
so
all may benefit.

"Kay" wrote in message
...
Suzanne,

I do and I have tried the formula several different ways. One is to
use a
calculated field with the formula and the other is the table, formula
option.
I have tried replacing the cell reference withe the book mark name of
the
form field as well. A form has to be protected of course for forms and
sections...so how are you supposed to be able to update it.


"Suzanne S. Barnhill" wrote:

Do you have "Calculate on exit" checked in each form field that
contributes
to the calculation?

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so
all may benefit.

"Kay" wrote in message
...
Hello Everyone,

I have a pretty complex form that uses many tables, formfields,
macros,
etc.
and I have two major problems:
In one of the sections I have a table with number fields and
formulas
to
calculate.

B4*B5* in one field and B6 =if(B6=0,0,(B7*3)+b3) in another. In
fact
each
column have the same relative formulas. They are set up as
calculated
formfields.....not just formulas in the cell. When first created
they
seemed
fine, but now they do not update at all. The form is protected as
it
should
be and I have a macro set at the end of the form to unprotect,
spell
check
and I assumed update the calculation fields. It just no longer
works.
I
thought there was a line of code in the macro that selected all of
the
document and updated, but it seems to have been taken out somehow.
Here
is
the code behind the macro courtesy MVPs.

Sub FormsSpellCheck()

' If document is protected, Unprotect it.
If ActiveDocument.ProtectionType wdNoProtection Then
ActiveDocument.Unprotect Password:="not2day"
End If

' Set the language for the document.
Selection.WholeStory
Selection.LanguageID = wdEnglishUS

' Perform Spelling/Grammar check.
If Options.CheckGrammarWithSpelling = True Then
ActiveDocument.CheckGrammar
Else
ActiveDocument.CheckSpelling
End If

' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
NoReset:=True
End If

End Sub

The second problem has to do with the template and all of its
macros.
This
document of course has been saved as a template. All of the macros
are
stored in the template. A co-worker emailed me the template with
suggested
changes (Not tracking) and none of the macros were with the
template.
I
have
many macros for doing things like validation and adding new rows as
well
as
the spell check macro. I do not understand why this is happening
and
how
to
stop it. We need many people to download this template and use it
with
macros intact. I have other forms and they were also emailed to me
and
they
work fine.

Please help!!!!!!

Hi Kay,

The simple solution is to use equation/formula fields, rather than
formfields, for the calculations based on the formfield entries.

For more info on Word field maths, check out my Word Field Maths 'tutorial',
at:
http://www.wopr.com/cgi-bin/w3t/show...?Number=365442

Cheers


"Kay" wrote in message
...
Doug,

Would you then recommend using excel, or is there a workaround for this
problem?

"Doug Robbins - Word MVP" wrote:

You will get errors if you use the result of one FormField calculation
as
the input to another calculation.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Kay" wrote in message
news
Suzanne,

You are certainly correct....but it doesn't do that. I have tried
recreating every field and I even created a macro that will update and
that
solves the problem for one row of formulas but not for the other. My
macro
moves to the calculated field, selects the whole row, updates and then
moves
to the next row and selects that row and updates. The first row
updates,
but
the second does not...probably because the second row is a calculation
that
uses the answer from the first calculation multiplied by 3 and + to
another
number field in the same table. I really did not want to use Excel in
this
document because it is already complex enough.

"Suzanne S. Barnhill" wrote:

A calculated field should update automatically if all its components
are
set
to "Calculate on exit." The catch is that "exit" means "tab out." If
you
don't tab (but instead just move the mouse), this isn't triggered.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup
so
all may benefit.

"Kay" wrote in message
...
Suzanne,

I do and I have tried the formula several different ways. One is
to
use a
calculated field with the formula and the other is the table,
formula
option.
I have tried replacing the cell reference withe the book mark name
of
the
form field as well. A form has to be protected of course for forms
and
sections...so how are you supposed to be able to update it.


"Suzanne S. Barnhill" wrote:

Do you have "Calculate on exit" checked in each form field that
contributes
to the calculation?

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so
all may benefit.

"Kay" wrote in message
...
Hello Everyone,

I have a pretty complex form that uses many tables, formfields,
macros,
etc.
and I have two major problems:
In one of the sections I have a table with number fields and
formulas
to
calculate.

B4*B5* in one field and B6 =if(B6=0,0,(B7*3)+b3) in another.
In
fact
each
column have the same relative formulas. They are set up as
calculated
formfields.....not just formulas in the cell. When first
created
they
seemed
fine, but now they do not update at all. The form is protected
as
it
should
be and I have a macro set at the end of the form to unprotect,
spell
check
and I assumed update the calculation fields. It just no longer
works.
I
thought there was a line of code in the macro that selected all
of
the
document and updated, but it seems to have been taken out
somehow.
Here
is
the code behind the macro courtesy MVPs.

Sub FormsSpellCheck()

' If document is protected, Unprotect it.
If ActiveDocument.ProtectionType wdNoProtection Then
ActiveDocument.Unprotect Password:="not2day"
End If

' Set the language for the document.
Selection.WholeStory
Selection.LanguageID = wdEnglishUS

' Perform Spelling/Grammar check.
If Options.CheckGrammarWithSpelling = True Then
ActiveDocument.CheckGrammar
Else
ActiveDocument.CheckSpelling
End If

' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
NoReset:=True
End If

End Sub

The second problem has to do with the template and all of its
macros.
This
document of course has been saved as a template. All of the
macros
are
stored in the template. A co-worker emailed me the template
with
suggested
changes (Not tracking) and none of the macros were with the
template.
I
have
many macros for doing things like validation and adding new
rows as
well
as
the spell check macro. I do not understand why this is
happening
and
how
to
stop it. We need many people to download this template and use
it
with
macros intact. I have other forms and they were also emailed
to me
and
they
work fine.

Please help!!!!!!