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 / Programming / November 2006

Tip: Looking for answers? Try searching our database.

Using VBA to SUM 3 columns in a Word Table

Thread view: 
Enable EMail Alerts  Start New Thread
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

Rate this thread:






 
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.