Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
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!!!!!! |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
PLease Help!!!Formulas not updating and macros disappearing
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!!!!!! |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
PLease Help!!!Formulas not updating and macros disappearing
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!!!!!! |
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
PLease Help!!!Formulas not updating and macros disappearing
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!!!!!! |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
PLease Help!!!Formulas not updating and macros disappearing
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!!!!!! |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
PLease Help!!!Formulas not updating and macros disappearing
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!!!!!! |
#7
Posted to microsoft.public.word.tables
|
|||
|
|||
PLease Help!!!Formulas not updating and macros disappearing
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!!!!!! |
#8
Posted to microsoft.public.word.tables
|
|||
|
|||
PLease Help!!!Formulas not updating and macros disappearing
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!!!!!! |