Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
summing a column of values from dropdown boxes
Word 2000: I have a table with two form field dropdown boxes in a
column where the user selects a single value from the dropdown box in each. I want to show the resulting sum total for the two boxes in the bottom cell of the column. I've tried inserting a table formula (=b1+b2) -- doesn't work; it's always equal to zero even though I have checked the "Calculate on exit" box in b1 and b2 properties. I've tried writing a macro: ActiveDocument.FormFields("Coltotal").Result = ActiveDocument.FormFields("Dropdown1").Value + ActiveDocument.FormFields("Dropdown2").Value This also doesn't work even though I've set "Run macro on exit" in b1 and b2. Here I'm also not sure how to properly define the Coltotal field -- what type of field is it? Dropdown doesn't seem right, how do I create the appropriate field type in the table cell? Would the answer be any different if the dropdown boxes and result box were not inside a table? Any help appreciated. Thx. /Bill |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
summing a column of values from dropdown boxes
The macro is the right idea, but you need to fix the syntax:
ActiveDocument.FormFields("ColTotal").Result = _ Val(ActiveDocument.FormFields("Dropdown1").Result) + _ Val(ActiveDocument.FormFields("Dropdown2").Result) Note that there's a space before each underscore at the ends of the first two lines. That's required. For the ColTotal field, use a text form field. In its Properties dialog, uncheck the box for "Fill-in enabled" to prevent users from typing in a number different from the sum of the dropdowns. It makes no difference to the macro whether the fields are in a table or in plain text. But the table, if autofit is turned off, will prevent other text around the form fields from moving when the amount of text in the fields changes. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. On 6 Oct 2006 15:47:33 -0700, wrote: Word 2000: I have a table with two form field dropdown boxes in a column where the user selects a single value from the dropdown box in each. I want to show the resulting sum total for the two boxes in the bottom cell of the column. I've tried inserting a table formula (=b1+b2) -- doesn't work; it's always equal to zero even though I have checked the "Calculate on exit" box in b1 and b2 properties. I've tried writing a macro: ActiveDocument.FormFields("Coltotal").Result = ActiveDocument.FormFields("Dropdown1").Value + ActiveDocument.FormFields("Dropdown2").Value This also doesn't work even though I've set "Run macro on exit" in b1 and b2. Here I'm also not sure how to properly define the Coltotal field -- what type of field is it? Dropdown doesn't seem right, how do I create the appropriate field type in the table cell? Would the answer be any different if the dropdown boxes and result box were not inside a table? Any help appreciated. Thx. /Bill |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
summing a column of values from dropdown boxes
Thanks Jay, that helps alot.
One last problem: I really can't use use a text form field because the document is going to be mail-merged to email. Mail-merging causes all textform fields to be lost. (I know there's a workaround on the MS support site, but it actually only works when you're mail-merging to a new document, not to email.) Maybe I could define a bookmark where I want the result, and then have the macro move to the designated spot to insert the result. I'm not too familiar with how bookmarks work, though. Any ideas on how this would be written in a macro? Thx. /Bill Jay Freedman wrote: The macro is the right idea, but you need to fix the syntax: ActiveDocument.FormFields("ColTotal").Result = _ Val(ActiveDocument.FormFields("Dropdown1").Result) + _ Val(ActiveDocument.FormFields("Dropdown2").Result) Note that there's a space before each underscore at the ends of the first two lines. That's required. For the ColTotal field, use a text form field. In its Properties dialog, uncheck the box for "Fill-in enabled" to prevent users from typing in a number different from the sum of the dropdowns. It makes no difference to the macro whether the fields are in a table or in plain text. But the table, if autofit is turned off, will prevent other text around the form fields from moving when the amount of text in the fields changes. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. On 6 Oct 2006 15:47:33 -0700, wrote: Word 2000: I have a table with two form field dropdown boxes in a column where the user selects a single value from the dropdown box in each. I want to show the resulting sum total for the two boxes in the bottom cell of the column. I've tried inserting a table formula (=b1+b2) -- doesn't work; it's always equal to zero even though I have checked the "Calculate on exit" box in b1 and b2 properties. I've tried writing a macro: ActiveDocument.FormFields("Coltotal").Result = ActiveDocument.FormFields("Dropdown1").Value + ActiveDocument.FormFields("Dropdown2").Value This also doesn't work even though I've set "Run macro on exit" in b1 and b2. Here I'm also not sure how to properly define the Coltotal field -- what type of field is it? Dropdown doesn't seem right, how do I create the appropriate field type in the table cell? Would the answer be any different if the dropdown boxes and result box were not inside a table? Any help appreciated. Thx. /Bill |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
summing a column of values from dropdown boxes
Excellent. Thanks again, Jay, I've really learned alot from this.
Jay Freedman wrote: You can use a bookmark to insert the result, but there's a twist... Because the form is protected, you can't simply insert text at a bookmark. If you tried, you'd get an error message, "You are not allowed to edit this region because document protection is in effect." So the macro has to unprotect the form, insert the text, and reprotect the form: Dim Rg As Range With ActiveDocument If .ProtectionType wdNoProtection Then .Unprotect End If Set Rg = .Bookmarks("ColTotal").Range Rg.Text = _ Val(.FormFields("Dropdown1").Result) + _ Val(.FormFields("Dropdown2").Result) .Bookmarks.Add Name:="ColTotal", Range:=Rg .Protect Type:=wdAllowOnlyFormFields, NoReset:=True End With For an explanation of why I had to re-add the bookmark, see http://www.word.mvps.org/FAQs/Macros...AtBookmark.htm. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. On 6 Oct 2006 20:19:33 -0700, wrote: Thanks Jay, that helps alot. One last problem: I really can't use use a text form field because the document is going to be mail-merged to email. Mail-merging causes all textform fields to be lost. (I know there's a workaround on the MS support site, but it actually only works when you're mail-merging to a new document, not to email.) Maybe I could define a bookmark where I want the result, and then have the macro move to the designated spot to insert the result. I'm not too familiar with how bookmarks work, though. Any ideas on how this would be written in a macro? Thx. /Bill Jay Freedman wrote: The macro is the right idea, but you need to fix the syntax: ActiveDocument.FormFields("ColTotal").Result = _ Val(ActiveDocument.FormFields("Dropdown1").Result) + _ Val(ActiveDocument.FormFields("Dropdown2").Result) Note that there's a space before each underscore at the ends of the first two lines. That's required. For the ColTotal field, use a text form field. In its Properties dialog, uncheck the box for "Fill-in enabled" to prevent users from typing in a number different from the sum of the dropdowns. It makes no difference to the macro whether the fields are in a table or in plain text. But the table, if autofit is turned off, will prevent other text around the form fields from moving when the amount of text in the fields changes. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. On 6 Oct 2006 15:47:33 -0700, wrote: Word 2000: I have a table with two form field dropdown boxes in a column where the user selects a single value from the dropdown box in each. I want to show the resulting sum total for the two boxes in the bottom cell of the column. I've tried inserting a table formula (=b1+b2) -- doesn't work; it's always equal to zero even though I have checked the "Calculate on exit" box in b1 and b2 properties. I've tried writing a macro: ActiveDocument.FormFields("Coltotal").Result = ActiveDocument.FormFields("Dropdown1").Value + ActiveDocument.FormFields("Dropdown2").Value This also doesn't work even though I've set "Run macro on exit" in b1 and b2. Here I'm also not sure how to properly define the Coltotal field -- what type of field is it? Dropdown doesn't seem right, how do I create the appropriate field type in the table cell? Would the answer be any different if the dropdown boxes and result box were not inside a table? Any help appreciated. Thx. /Bill |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
summing a column of values from dropdown boxes
You're certainly welcome! Glad I could help.
On 7 Oct 2006 06:48:47 -0700, wrote: Excellent. Thanks again, Jay, I've really learned alot from this. Jay Freedman wrote: You can use a bookmark to insert the result, but there's a twist... Because the form is protected, you can't simply insert text at a bookmark. If you tried, you'd get an error message, "You are not allowed to edit this region because document protection is in effect." So the macro has to unprotect the form, insert the text, and reprotect the form: Dim Rg As Range With ActiveDocument If .ProtectionType wdNoProtection Then .Unprotect End If Set Rg = .Bookmarks("ColTotal").Range Rg.Text = _ Val(.FormFields("Dropdown1").Result) + _ Val(.FormFields("Dropdown2").Result) .Bookmarks.Add Name:="ColTotal", Range:=Rg .Protect Type:=wdAllowOnlyFormFields, NoReset:=True End With For an explanation of why I had to re-add the bookmark, see http://www.word.mvps.org/FAQs/Macros...AtBookmark.htm. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. On 6 Oct 2006 20:19:33 -0700, wrote: Thanks Jay, that helps alot. One last problem: I really can't use use a text form field because the document is going to be mail-merged to email. Mail-merging causes all textform fields to be lost. (I know there's a workaround on the MS support site, but it actually only works when you're mail-merging to a new document, not to email.) Maybe I could define a bookmark where I want the result, and then have the macro move to the designated spot to insert the result. I'm not too familiar with how bookmarks work, though. Any ideas on how this would be written in a macro? Thx. /Bill Jay Freedman wrote: The macro is the right idea, but you need to fix the syntax: ActiveDocument.FormFields("ColTotal").Result = _ Val(ActiveDocument.FormFields("Dropdown1").Result) + _ Val(ActiveDocument.FormFields("Dropdown2").Result) Note that there's a space before each underscore at the ends of the first two lines. That's required. For the ColTotal field, use a text form field. In its Properties dialog, uncheck the box for "Fill-in enabled" to prevent users from typing in a number different from the sum of the dropdowns. It makes no difference to the macro whether the fields are in a table or in plain text. But the table, if autofit is turned off, will prevent other text around the form fields from moving when the amount of text in the fields changes. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. On 6 Oct 2006 15:47:33 -0700, wrote: Word 2000: I have a table with two form field dropdown boxes in a column where the user selects a single value from the dropdown box in each. I want to show the resulting sum total for the two boxes in the bottom cell of the column. I've tried inserting a table formula (=b1+b2) -- doesn't work; it's always equal to zero even though I have checked the "Calculate on exit" box in b1 and b2 properties. I've tried writing a macro: ActiveDocument.FormFields("Coltotal").Result = ActiveDocument.FormFields("Dropdown1").Value + ActiveDocument.FormFields("Dropdown2").Value This also doesn't work even though I've set "Run macro on exit" in b1 and b2. Here I'm also not sure how to properly define the Coltotal field -- what type of field is it? Dropdown doesn't seem right, how do I create the appropriate field type in the table cell? Would the answer be any different if the dropdown boxes and result box were not inside a table? Any help appreciated. Thx. /Bill -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bullet / Number are separated from its text on column break | Page Layout | |||
Tutorial for Dropdown boxes in a Word table on the Web? | New Users | |||
Hyperlinks in Word Dropdown Boxes | Microsoft Word Help | |||
Expanding dropdown boxes - office. | Microsoft Word Help | |||
Dragging table column resets | Tables |