MS Office Forum / Word / Programming / November 2006
Using VBA to SUM 3 columns in a Word Table
|
|
Thread rating:  |
doctorjones_md - 30 Oct 2006 01:09 GMT I have a Word Table in my Active Document that I'd like to SUM using a cmdCalculateTotal button on a form. Can somone please tell me how to approach this -- for example -- how would I write the VBA code to calculate Cells C3:C16 of the table? The Range of the calculated area is subject to change (depending on the choices the user makes (selecting checkboxes and radio buttons), so it seems that I would need to calculate the range of the column (after all selections have been made, and ADD a Summary Row to the table to calculate the values.
I'm just not sure the easiest way to approach this
NOTE: I'm trying to approach this project in blocks -- I'm still working on the issue of getting the table values (selected by the user) to populate in the Active Document -- I'm close, but not there yet -- Summing the values would be the next step.
Any help will be greatly appreciated.
Thanks in advance
Shane
doctorjones_md - 30 Oct 2006 02:45 GMT >I have a Word Table in my Active Document that I'd like to SUM using a >cmdCalculateTotal button on a form. Can somone please tell me how to [quoted text clipped - 17 lines] > > Shane Jay Freedman - 30 Oct 2006 02:55 GMT You may not need VBA. Instead, try a SUM field. See http://word.mvps.org/FAQs/TblsFldsFms/TotalColumn.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.
>I have a Word Table in my Active Document that I'd like to SUM using a >cmdCalculateTotal button on a form. Can somone please tell me how to [quoted text clipped - 17 lines] > >Shane doctorjones_md - 30 Oct 2006 03:22 GMT Thanks for the Quick Reply and info Jay -- I'm looking it over -- at quick read, this seems like a viable option -- will let you know if it works out -- thanks again :)
> You may not need VBA. Instead, try a SUM field. See > http://word.mvps.org/FAQs/TblsFldsFms/TotalColumn.htm. [quoted text clipped - 31 lines] >> >>Shane Doug Robbins - Word MVP - 30 Oct 2006 05:54 GMT Your approaching this project in blocks is probably NOT going to result in you getting the best advice.
If you describe the total project, you will be more likely to get better assistance.
You obviously have some pre-conceived ideas about how each step should be undertaken and they may not be the best.
 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 have a Word Table in my Active Document that I'd like to SUM using a >cmdCalculateTotal button on a form. Can somone please tell me how to [quoted text clipped - 17 lines] > > Shane doctorjones_md - 30 Oct 2006 10:44 GMT Doug,
I agree with you Doug. To be honest (and fair to all who have offered help/advice) -- I outlined the scope of my project in my initial post, but what I got (as a response) was: ======================= "Well that is a lot of material to absorb and without the document it would be a lot of work to sort out here in the newsgroups. To tell you the truth I am simply not up to it gratis at present." ======================= , so I got the impression that people in these groups were more prone to offer assistance if you gave it to them in chunks. In say this, I've seen posts where people have submitted rather lenghty (and detailed) posts, and several members/MVPs gave their inputs.
This was my detailed post on 10/24/06 -- which was address to you Doug ;) ================================== I've added several additional forms (accessed from frmMain) which have checkboxes and radio buttons fo the user makes his or her selections. The the data in the corresponding bookmarks should then be inserted into the Word.dot when the OK button is clicked. Here are my questions:
1. I need for the SUBMIT button on frmMain to be disabled until the user makes at least (1) selection from the subordinate forms -- what is the best way to achieve this?
2. I need for these (10) forms to insert the corresponding bookmarks into a Table (1) principal Word.dot -- What I did in the Word.dot was to take your advice and bookmark the entire row (spanning 4 columns) -- I'm lost on how to incorporate your code (ActiveDocument.Bookmarks("bookmarkname").Range.Cells(1).Range). (This was your suggestion Doug -- I just can't seem to get it to work)
I'm kinda wrapped around the axle here -- Could you give me an idea what I need to do to incorportate this functionality -- for example:
Just a simple list of components needed ...
Word.dot - with sample code A couple of forms showing code to allow for selected checkboxes/radio buttons to populate bookmarks in the Word.dot (active document)
Question: Wouldn't it be much simplier to import the data from an Excel WB, and if so, what is the best way to associate the checkboxes/radio buttons on the forms to the Excel workbook (so that only the selected items/data is inserted into the Word.dot)?
Much Thanks In Advance
Shane
For your question 1, "Best" is subjective. Here is one way.
> Your approaching this project in blocks is probably NOT going to result in > you getting the best advice. [quoted text clipped - 26 lines] >> >> Shane Doug Robbins - Word MVP - 30 Oct 2006 14:42 GMT Sorry, but that post purporting to give the whole picture was about halfway through your series of posts and it does not describe what you are trying to do from start to finish.
My recent suggestion that you needed to provide the full story was made because I do not think that you are going in the right direction.;
 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, > [quoted text clipped - 82 lines] >>> >>> Shane Doctorjones_md - 30 Oct 2006 18:03 GMT Doug,
I apologize if I'm not being clear in what I'm trying to achieve with this project. It's very easy (with a variety of input from various members) to choose a lesser course on a project (in terms of overall functionality).
Let me try again to explain what I'm doing:
1. I have made a Word.dot template -- on this template, I have the following: a.frmMain -- which has the following: -- several Input Text boxes to capture data which is then inserted into various bookmarks in the Active Document ---- ALL this works fine at present -- 2 Command Buttons -- these command buttons open various forms (15 to be exact) where the user should make selections (based on needs) -- several Radio Buttons where the user makes a selection and Text from an underlying doc is inserted into bookmarks in the Active Document ---- ALL this works fine at present -- A List Box (I think I used some of your code here ;) which lists all the bookmarks in the Active Document. Here's how things currently work:
***** User selects Linen Bookmark in the List Box -- this moves the cursor to the Linen bookmark in the Active Document ***** User clicks the cmdProducts button which opens frmProducts (which has the following code) Private Sub cbxLinen_Click() If Me.cbxLinen.Value = True Then
ActiveDocument.Bookmarks("Linen").Range.InsertAfter Linen sFilePath = ActiveDocument.AttachedTemplate.Path & "\Linen.doc" Selection.InsertFile sFilePath, , False, False
End If End Sub
Private Sub cbxPillows_Click() If Me.cbxThawte.Value = True Then
ActiveDocument.Bookmarks("Pillows").Range.InsertAfter Pillows sFilePath = ActiveDocument.AttachedTemplate.Path & "\Pillows.doc" Selection.InsertFile sFilePath, , False, False
End If End Sub
Private Sub cbxMattresses_Click() If Me.cbxMattresses.Value = True Then
ActiveDocument.Bookmarks("Matttresses").Range.InsertAfter Mattresses sFilePath = ActiveDocument.AttachedTemplate.Path & "\Mattresses.doc" Selection.InsertFile sFilePath, , False, False
End If End Sub
Private Sub cmdCancel_Click() Unload Me End Sub
Private Sub cmdOK_Click() Application.ScreenUpdating = True Me.Hide
End Sub
Private Sub frmSSL_Initialize() Me.cbxLinen.Value = Null Me.cbxPillows.Value = Null Me.cbxMattresses.Value = Null
End Sub ====================================== The above code works OK, but I'm sure there's a better way to handle this (rather than having to create a separate document for each Product (ie, Linen.doc, Pillows.doc, Mattresses.doc) NOTE -- I couldn't get the [Cell(row, column).Range.Text property of the table] suggestion that Jonathan West offered, or the [ActiveDocument.Bookmarks("bookmarkname").Range.Cells(1).Range to refer to the first cell in the bookmark, then increment the number for the remaining cells.] suggestion that you made on 10/17/06 to work -- Doug, could you please break down the ActiveDocument.Bookmarks("bookmarkname").Range.Cells(1).Range entry for me and let me know how to apply it to my project -- I'm struggling with understanding the directions you've provided
2. After the series of forms have been opened and the proper selections made, the Active Document now has a list of Products Selected, with the following fields: a. Description b. Details c. Cost without shipping d. Cost with shipping e. Quantity I need to TOTAL columns C, D, and E in a Product Summary line at the bottom of the Products Table (This was the last post that I made this morning)
I hope I've been concise in my requirements Doug -- it's not my intent to be vague, rather, to be considerate of the time you all take in helping folks like me -- I REALLY do appreciate all the assistance you give me! :)
Thanks in advance for any additional help you can offer.
Shane
-- I used this feature because I didn't know another way to move the cursor to the desired bookmarks when the b.
> Sorry, but that post purporting to give the whole picture was about > halfway through your series of posts and it does not describe what you are [quoted text clipped - 92 lines] >>>> >>>> Shane Doug Robbins - Word MVP - 30 Oct 2006 20:26 GMT Let's try a plain English description without any code.
The problem with your code is that I can't tell what everything is. For example what sort of controls are cbxLinen, cbxPillows, cbxMattresses? Don't answer that for my benefit. It would be better to describe what you want to be done, not how you think it should be done.
For example, I can't imagine what the "needs" are that are apparently satisfied by the 2 Command Buttons -- these command buttons open various forms (15 to be exact) where the user should make selections (based on needs), but I am sure that there is a better way to do it.
I suspect that you should be using something more along the lines of the system used in the following:
http://groups-beta.google.com/group/microsoft.public.word.vba.userforms/browse_f rm/thread/f3f67faa8297aa66/bf86a5657fe2c34e?lnk=st&q=(Doug%27s+AND+Combobox+AND+ Code)+group%3Amicrosoft.public.word.vba*&rnum=1&hl=en#bf86a5657fe2c34e
 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, > [quoted text clipped - 209 lines] >>>>> >>>>> Shane Doctorjones_md - 02 Nov 2006 21:07 GMT Doug,
Sorry for the delay in my reply -- I needed to sit back and analyze what I already have -- Here's what I'm trying to do with this project:
I'm trying to create the following -- the final step involves removing ALL macros from the Active Document when saved
I have a Word.dot that has a variety of BookMarks in it -- PLUS a series of forms which allow the user to: a. Fill-In Text Boxes which populated bookmarked areas in the Header and Body of the Active Document **** Eventually, I will tie into our database to pull much of this data to populate these fields in the active document b. Click Checkboxes and Radio Buttons to add Paragraphs of Text in the body of the Active Document **** As the user makes these selections, the Body of the Active Document is customized for this user c. Click Checkboxes and Radio Buttons to add data to the Active Document (comprised of 5 columns of data from a Word Table in a separate Word Document) **** This table that is be created lists the items the user has selected -- 3 colums will be calculated and summed in a Summary Row
The problem I have here is that if I select more than 1 checkbox or radio-button item, the (5) columns of data from the 2nd selection gets nested in Cell (1) of the Row that was added to the Active Document in the 1st selection. Is there a way that I can force the code in subsequent selections to insert a completely NEW row into the table (not nest all 5 cells of NEW data into Row(1) Cell(1) )?
The last thing that needs to occur is for all Macros to be removed from the Active Document when saving it as a Word.doc -- how can I do this?
Much thanks in advance!
Shane
> Let's try a plain English description without any code. > [quoted text clipped - 228 lines] >>>>>> >>>>>> Shane Doug Robbins - Word MVP - 02 Nov 2006 22:13 GMT Sorry Doc, that is not plain English enough. You already have pre-conceived ideas about how things should be done (check boxes and radio buttons) and I strongly suspect that they are not the things that you should be using. But, I do not know enough about what it is to be really sure.
Re your final thing about macros being removed from the active document, if the macros are in the template from which the document is created by using File>New, they will not be in the active document and hence there will not be any macros to remove.
 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, > [quoted text clipped - 267 lines] >>>>>>> >>>>>>> Shane Doctorjones_md - 03 Nov 2006 00:18 GMT Doug (or anyone else who might have an idea how I can bettter explain this),
I don't mean to sound elementary with this, but (from my understanding): 1. Radio-Buttons provide the user (2) and ONLY (2) mutually exclusive options -- are we in agreement with this? If not, see: http://www.useit.com/alertbox/20040927.html 2. Checkboxes offer a variety of choices/selections
Your comments: "Sorry Doc, that is not plain English enough. You already have pre-conceived ideas about how things should be done (check boxes and radio buttons) and I strongly suspect that they are not the things that you should be using."
What would you suggest I use when the user is to make a choice between (2) and ONLY (2) options? (ie: Require Insurance? Yes/No) What wouldyou suggest I use when the user is allowed to make a variety of choices? (ie: Leather Seats, CD Player, Cruise Control, AC, ...)
I'm not trying to be condescending here Doug -- the expertise and advice you offer up to folks in here shows you to be quite intelligent -- how better can I explain to you what I'm trying to do?
SCENARIO -- Imagine (if you will) you own a business (say a Taco Stand) -- You have your Menu Items in a Word Table, and you want to create a Banquet Catering Proposal for a client. You client calls in, and you access a Word.doc on your computer -- how are we up to this point -- still with me here? ;)
Now this may seem cosmic, but I want to have a series of forms (called by command buttons on frmMain) give my catering rep the ability to select Menu Items (Checkboxes) and Service Options (Radio Buttons) such as "Delivery: Yes/No -- still with me?
Pre-conceived ideas? Yes, I think every problem/solution scenario has a certain amount of pre-conception involved (which isn't necessarily a bad thing) ;)
If we're still not hitting on all cylinders (after this exhaustive explanation) Dave, then perhaps you could enlighten me as to how you would tackle the scenario I described above.
Just a thought -- perhaps you have a pre-conceived notion about how doctors think -- it may help to tell you that I'm not a doctor ;)
> Sorry Doc, that is not plain English enough. You already have > pre-conceived ideas about how things should be done (check boxes and radio [quoted text clipped - 282 lines] >>>>>>>> >>>>>>>> Shane Doug Robbins - Word MVP - 03 Nov 2006 05:43 GMT SCENARIO -- Imagine (if you will) you own a business (say a Taco Stand) -- You have your Menu Items in a Word Table, and you want to create a Banquet Catering Proposal for a client. You client calls in, and you access a Word.doc on your computer -- how are we up to this point -- still with me here? ;)
HERE IS WHERE I THINK YOU ARE GOING OFF THE RAILS, OR AS YOU PUT IT, OUT OF THIS PLANET
Now this may seem cosmic, but I want to have a series of forms (called by command buttons on frmMain) give my catering rep the ability to select Menu Items (Checkboxes) and Service Options (Radio Buttons) such as "Delivery: Yes/No -- still with me?
I am almost certain that you should be using something along the lines of the information in the thread at
http://groups-beta.google.com/group/microsoft.public.word.vba.userforms/browse_f rm/thread/f3f67faa8297aa66/bf86a5657fe2c34e?lnk=st&q=(Doug%27s+AND+Combobox+AND+ Code)+group%3Amicrosoft.public.word.vba*&rnum=1&hl=en#bf86a5657fe2c34e
using a multi-select ListBox at one point rather than a combo box
I suspect that you have a situation where you have
Type of food stand - Say Taco, Pizza, Pasta, Ice Cream, etc in a combobox
User selects type of stand, then that populates a second combobox with applicable types for example if Pizza was selected, the second combo box might contain
Pepperoni, Capriciosa, Regina, Quatre Staglione, etc.
Then you might have a listbox that was populated with extas
extra cheese, anchovies, jalapenos
from which the use could select multiple items
etc. etc
But, if they had chosen Ice Cream, the second combo box would have been populated with the flavours
Chocolate, Coffee, Strawberry, Rum and Raisin
and the List box with optional toppings, such as
Nuts, Whipped Cream and Other Delights (Herb Alpert), etc
 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 anyone else who might have an idea how I can bettter explain > this), [quoted text clipped - 331 lines] >>>>>>>>> >>>>>>>>> Shane
|
|
|