Using tables in Word with VBA - SUM function?

[Mediocre Egg]

I'm desiging a credit card receipt userform for our customer service representatives to use.

Part of the template for the form is a table. The table contains 6 rows, and 4 columns. This is a standard receipt table with Quantity, Cost, Description, and so on. The bottom-right cell contains the Total for the purchase.

Rather than having the reps manually enter the total amount upon entering the individual costs, is there a way in VBA to have the Total field auto-populate with the sum of the sub-totals?

I would also like to repeat this number for another field, which re-states how much has been charged to the credit card.

If someone could shed some light on this, it would be much appreciated. I am still new at VBA :)

Edit: Also, I am giving the reps the option to include up to four purchased items in this table. Is there a more elegant way of handling this rather than create my userform with each of the 4 Text boxes and Comboboxes repeated 4 times? In other words, would there be a way to have the 4 fields (Quantity, Term, Cost, Publication) and an "Add" button, which would throw it in the table. Once the "Add" button is clicked, the 4 fields would blank out and be ready for the next item. The "Add" button would be in addition to the standard "Submit", "Clear", and "Cancel" buttons.

[lucas]

I would suggest you use the appropriate application...Excel.
it can be done by inserting an excel object into the word doc and I think some limited amout of formula used in tables is applicable also.

I would use excel for all of this.

[Mediocre Egg]

I was hoping to do it in Word because I've finally got a slight foothold on this and I wanted more practice :(

I'm not sure how I would go about even starting this in Excel. I like the Word method because of the ability to use Bookmarks.

[lucas]

Could you have the math done in the userform before it's added to the page?

[lucas]

I'm still not real clear on what you are trying to do but please take a minute to look at this example to see if it is close to what you are looking for. Much customization to the appearance is possible.

[fumei]

Rather than having the reps manually enter the total amount upon entering the individual costs, is there a way in VBA to have the Total field auto-populate with the sum of the sub-totals?Yes there is.

I would also like to repeat this number for another field, which re-states how much has been charged to the credit card.This also can be done.

In other words, would there be a way to have the 4 fields (Quantity, Term, Cost, Publication) and an "Add" button, which would throw it in the table. Once the "Add" button is clicked, the 4 fields would blank out and be ready for the next item. The "Add" button would be in addition to the standard "Submit", "Clear", and "Cancel" buttons.This is also not particularly difficult.

All that being said.....it would still be better do it in Excel. Excel does numbers. Word does words.

[Mediocre Egg]

Thanks for all of the advice so far. Sorry I didn't respond yesterday - I made that post toward the end of my work day and I don't have access to any Office apps at home.

I guess I might not have explained myself very well originally in my intent. And I apologize if this is annoying - but it's driving me crazy not being able to do this.

That Excel example you posted lucas is close to what I am trying to do but not quite.

This is what my UserForm looks like:
http://img524.imageshack.us/img524/3870/screenshot00275pu5.gif

And this is my table:
http://img440.imageshack.us/img440/5499/tabledv7.gif

Scenario:

The user would open the Word template and the UserForm would open. They would enter in the customer's name, account number, and address into the appropriate fields. Next they would enter the Publication, Term length, Quantity, and Cost.

Now, at this point they could either click the Submit button to dump everything into the document, ready for sending, or they could click the "Add Order" button to add multiple lines to the receipt.

The Add Order button would perform several operations on click: It would unload the four mentioned fields into the table into their respective cells. It would then clear out the four UserForm fields and place the cursor back in the first field (Publication). Upon submitting the next line item it would move down one row on the table and place the next four fields there. It would also keep a running total in the "Total" field. I imagine this would be done by calculating Quantity * Cost.


I would use Excel but I'm not sure if I can get the same appearance in Excel with a document that I can with Word. But I do plan on looking into the Excel options throughout today (it's still early at least). If I can get the appearance of the receipt looking nice I will go for it.

I tried to upload my Word file to the forums to see if the same look is possible in Excel but the file size is too large.

Again, thanks for the help so far - I am envious of everybody's skill here.

[fumei]

ZIP your file to post it up here.

This can be done in Word, although, generally speaking, it would still be better in Excel. The userform itself can be exactly the same in either Excel, or Word.

I have a question though.

The userform has Name, and Address etc...but your table does not. WHAT are you doing with the identification data from the userform??

Oh, and don't hang on to your envy. We all started from nothing. It takes time and practice, just like anything else. If you really want to, you can be as skillful as anyone here. VBA is not that hard, although it can be....odd at times. And as Steve says....

[Mediocre Egg]

ZIP your file to post it up here.

This can be done in Word, although, generally speaking, it would still be better in Excel. The userform itself can be exactly the same in either Excel, or Word.

I have a question though.

The userform has Name, and Address etc...but your table does not. WHAT are you doing with the identification data from the userform??

Oh, and don't hang on to your envy. We all started from nothing. It takes time and practice, just like anything else. If you really want to, you can be as skillful as anyone here. VBA is not that hard, although it can be....odd at times. And as Steve says....

Okay - I zipped up my example file and it should be attached to this reply.

The table screenshot I included before was cropped to focus on the table. The Name, Address, Account Number fields on the document weren't in that screenshot.

I'm sure my code is kind of a mess right now, as I am still figuring stuff out.

Also, I found the following two sites: here (http://www.microsoft.com/technet/scriptcenter/resources/officetips/jul05/tips0726.mspx), and here (http://www.microsoft.com/technet/scriptcenter/resources/officetips/aug05/tips0802.mspx), which explain how to insert tables and formulas into Word. The example that I uploaded just now does not have this included - my table is a simple drawn table, which I used before realizing the apparrent complexity of this project. I do plan on utilizing the table and formula methods included in those links.

On that first linked tutorial though they use the objTable.Rows.Add() method to add extra rows but for some reason even copying and pasting their entire code generates a syntax error because of that method. Is something wrong with their tutorial? That Rows.Add method would be helpful to me I bet. (I think I just figured it out. I added objTable.Rows.Add() = "" to the method.)



Any advice is appreciated!

[fumei]

Ah, you got a problem. But first a question.

The Add button just clears your controls on the userform.

Please explain clearly what Add and Submit are to do. This is what i think it is suppose to do.

Add - takes the values on the userform and put it on the last available row - but NOT the last row - of the table; clear the controls in order to allow another input.

Submit - does NOT take any values from the userform, except the identifiers, unloads the form. In other words, and this IS how it stands, Submit will not (does not) put anything into the table.

Be careful with your naming.

The commandbutton for Add Order is named cmbAddOrder. The name for the Cancel button is cmbCancel.

On the other hand, the name for the Submit button is cmdSubmit.

The name for the term length combobox is cmbTerm.


Commandbuttons are traditionally cmd
Comboboxes are cbo

If you really want to, you can use your own prefixes, but BE CONSISTENT.

Do not name some commandbuttons with a cmd prefix, and some commandbuttons with a cmb prefix...especially if you are using cmb for something else!

OK, now to your problem.

You have bookmarks for ONE line of the table. PubName1, Term1, Qty1, Cost1.

OK...what are you going to do if there are two, or three, or four, or five, or six, or seven rows? They won't have bookmarks.

Question: if the user only does have one input, one order, then there is going to be one line of the table filled in. That means rows 2, 3, 4, 5 are to remain blank? You want the table to have its minimum number of rows set?

That is OK, if that is what you want. I just want to be clear on what you want.

[fumei]

Aaacccckkkkk! I just realized you merged cells in the table!

Aaaccckkk! Unless you REALLY REALLY REALLY REALLY REALLY REALLY have to, do not merge cells in tables.

And even then...don't do it anyway.

Aaaaccckkk! You are not using Styles!

[Mediocre Egg]

That table in the template file is going away, to be replaced with a proper table created using the tutorial on those two pages I linked. I am still working out on how to do it. That means those bookmarks are going away too - I relied on them for another form I created but I realized they wouldn't be applicable for this table.

The Add button only clears the lines for now because that's the only part of the function I knew needed to happen - part of the function of the Add button will be to clear those four fields so it's ready for more orders.

So the Add button is supposed to only fill in those four fields mentioned earlier (Publication, Term, Quantity, and Cost) on the receipt table. It would then erase the four fields on the UserForm, readying them for the next order.



Add - takes the values on the userform and put it on the last available row - but NOT the last row - of the table; clear the controls in order to allow another input.

Submit - does NOT take any values from the userform, except the identifiers, unloads the form. In other words, and this IS how it stands, Submit will not (does not) put anything into the table.
Add - takes the value on the userform and put it on the first available row - but NOT the last row - of the table; clear the controls in order to allow another input.

Submit - doesn't put info into the table yet because I realized my table needed to be re-worked according to those two linked tutorials I posted before.



I'll have to rename the fields (cbo, cmb) for better consistency - thanks for noticing that.


And I'm not even sure what a style is... (I just Googled it though and I'll try adding them once my brain is working again.)


This is really getting confusing :dunno