Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Tables / May 2007

Tip: Looking for answers? Try searching our database.

Document with multiple tables, form fields, bookmarks and calculat

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Carl Bentz - 23 May 2007 04:51 GMT
I am trying to create a general "estimate" document for service advisors to
complete for their customers. Two of five tables (Tables 1 and 2) would
simply contain text form fields to simplify data entry. Two more of the
tables (Tables 3 and 4) would/could include multiple lines that would include
calculations (sum and percentage of) and both might need to have additional
rows added, if more options for service/repair work was required. One final
table would be an overview that calculated a subtotal, miscellaneous (a % of
calculation), a sales tax calculation and a grand total. The subtotal and
Miscellaneous items would be taken from Tables 3 and 4. - The users are
currently working with Word 2000. My development work is in Word 2003 at
present. -
Question 1. Is it possible to add additional rows to a table and have the
form field definitions be retained in the added rows?
Question 2. Can these calculations be done without resorting to a "button"
and VBA to initiation the math? [I did find an Update option that did the
calculations with individual tables, but could not get the information from
one table to another to work.]
Question 3. Can you tell me more about using Bookmarks and cell names in
order to refer to one or more cells in other tables in order to make
calculations - for example create a sum from a "totals" cell in Table 3 and a
"totals" cell in Table 4.
Thank you in advance for any ideas and assistance.
Doug Robbins - Word MVP - 23 May 2007 09:17 GMT
The following macro run on exit from the formfield in the last cell of the
last row of a table will add another row to the table and insert formfields
into each of the cells in that row:

' Macro created 02/02/03 by Doug Robbins
' To add a new row to a table containing formfields in every column
' automatically on exit from the last cell in the present last row of the
table
Dim rownum As Integer, i As Integer
Dim Response
Response = MsgBox("Do you want to add another row ?", vbYesNo)
If Response = vbYes Then    ' User chose Yes.
   ActiveDocument.Unprotect
   ActiveDocument.Tables(1).Rows.Add
   rownum = ActiveDocument.Tables(1).Rows.Count
   For i = 1 To ActiveDocument.Tables(1).Columns.Count
       ActiveDocument.FormFields.Add
Range:=ActiveDocument.Tables(1).Cell(rownum, i).Range,
Type:=wdFieldFormTextInput
   Next i
   ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
ActiveDocument.Tables(1).Columns.Count).Range.FormFields(1).ExitMacro =
"addrow"
   ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
1).Range.FormFields(1).Select
   ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End If
End Sub

It would need modifying if you need calculation type formfields in some of
the cells.

While it is possible to create such a form using form fields, I would really
recommend that it be done with a userform.

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

Signature

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

>I am trying to create a general "estimate" document for service advisors to
> complete for their customers. Two of five tables (Tables 1 and 2) would
[quoted text clipped - 24 lines]
> "totals" cell in Table 4.
> Thank you in advance for any ideas and assistance.
Carl Bentz - 28 May 2007 01:21 GMT
Doug, ( or whom ever)  Thank you for yur comments. I've been mulling over
what you said and perhaps the UserForm might be more to the point, but at
this point, I'm stuck with trying to do it work Word. And I'm really puzzled
if the addition of lines with form fields would solve the problem either.  
 I've tried to create as much of the Estimate.DOT as possible and will
attempt to attach it here if i can figure out how.  In the first table using
calculations I used 'bm1' and 'bm2' as column totals using a =sum(above) to
create a result in those BookMark fields. Similarly in the second table
'bm3', 'bm4', and 'bm5' are used with a similar calculation for Bookmark
field results.  The final table, then, references those book marks to do
subtotals, some auxiliary calculations, salestax and a final overall total.  
Well, that was the plan anyway, but obviously the syntax is wrong and "the
index too large"  with no indication in Microsoft info sources as to what
that means.

Oh well, the idea of attaching the file was a good one, but i do not see how
i could do it!  The following four error messages are from the table doing
the final calculations showing the error message and the formula causing them.

1 Sub-Total st1      !Index Too Large

{ =SUM(bm1,bm3,bm5) \# $#,##0.00 ) }

Miscellaneous st2      !Syntax Error, ;

{ =SUM(bm2;bm4) \# $#,##0.00 )}    also tried { =SUM(bm2,bm4) }  and got the
index too large message

Sales Tax 0.07525% st3    !Syntax Error, [

{ =SUM(0.07525*st1,0.07525*st2) \# $#,##0.00 }

Total  st4                       !Index Too Large

{ =SUM(st1,st2,st3) \# $#,##0.00 }

Updating values will still present a problem as well as additional lines as
2 items in each  of the service type tables will probably be insufficient,
but if i can get the calcuations working i'll be thrilled!  Thanks.

> The following macro run on exit from the formfield in the last cell of the
> last row of a table will add another row to the table and insert formfields
[quoted text clipped - 63 lines]
> > "totals" cell in Table 4.
> > Thank you in advance for any ideas and assistance.
Graham Mayor - 28 May 2007 06:39 GMT
If bm1 bm3 etc are bookmarks, you should have more success if you use

{ =SUM({REF bm1},{REF bm3},{REF bm5}) \# "$,0.00" ) }
or
{={REF bm1} + {Ref BM3} + {Ref BM5} \# "$,0.00"}

For the sales Tax

{={REF st2} * .07525 \ # "$,0.00"}

assuming that is the sum that represents your tax

ie to use the bookmarks in a calculation you need to use REF fields (which
may not require the Word REF, but will require field boundaries. Formular
fields like this are not used from the form fields manu but are inserted
from the insert > fields menu or manually using CTRL+F9 for the field
pairs - see also http://www.gmayor.com/formatting_word_fields.htm

Note that calculations from form fields will show an error until all the
form fields that make up the calculation have numeric content.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Doug, ( or whom ever)  Thank you for yur comments. I've been mulling
> over what you said and perhaps the UserForm might be more to the
[quoted text clipped - 116 lines]
>>> "totals" cell in Table 4.
>>> Thank you in advance for any ideas and assistance.
Carl Bentz - 28 May 2007 15:10 GMT
-> Graham   Thank you so much for your suggestions. About 15 seconds after I
posted my questions last night I realized my biggest problem was that I
didn't know exactly how to refer to the bookmarked values I had created. You
cleared that up very nicely. I am still fighting a couple of Syntax Errors at
this point, but I am sure that is something stupid on my part and if I stare
at it a bit more, I will see it.
Thanks again.
Also Thanks to Doug Robbins for his assistance!

> If bm1 bm3 etc are bookmarks, you should have more success if you use
>
[quoted text clipped - 60 lines]
> > insufficient, but if i can get the calcuations working i'll be
> > thrilled!  Thanks.
Graham Mayor - 28 May 2007 15:19 GMT
If you don't, grab the macro from http://www.gmayor.com/export_field.htm 
then use it to post the exact structure you are using.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> -> Graham   Thank you so much for your suggestions. About 15 seconds
> after I posted my questions last night I realized my biggest problem
[quoted text clipped - 79 lines]
>>> be insufficient, but if i can get the calcuations working i'll be
>>> thrilled!  Thanks.
Doug Robbins - Word MVP - 28 May 2007 06:43 GMT
Despam my email address and send me a copy of the document.

Signature

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

> Doug, ( or whom ever)  Thank you for yur comments. I've been mulling over
> what you said and perhaps the UserForm might be more to the point, but at
[quoted text clipped - 123 lines]
>> > "totals" cell in Table 4.
>> > Thank you in advance for any ideas and assistance.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.