Macro to assign value to and sum up check box forms 
Author Message
 Macro to assign value to and sum up check box forms

Hi Leah,

A lot of this would depend on exactly how your form
is structured.

The code below, if designated as the Entry Macro for
every checkbox formfield, will probably do it (I've
tested it a little but not thoroughly), *ASSUMING*:

 - All the checkboxes are in table cells.

 - Every table has uniform structure, i.e., same
   number of columns, with all checkboxes in a given
   column throughout all tables intended to be summed
   together.

 - First column contains no checkboxes.  (This would
   be trivial to change but I'm assuming Column 1
   would be for the questions or other non-checkbox
   material.)

If the tables vary in structure, this code would
probably get way more complicated and the task could
be monstrous, although doable.

HOWEVER....... asking that the totals update each time
a box is clicked is a pretty big deal.  Unless there's
a smarter way than using a universal On-Entry macro to
do that, even a small form on a fast computer may be
irritatingly slow.  I tested with only 5 tables and 36
checkboxes, with 4 checkbox columns (4 categories to
total), and even with screen-updating turned off,
Word does a silent little polka for at least 1 or 2
seconds after *each* mouseclick while the totals update.
If, as you say, the form is huge, then this delay could
be vastly magnified, and chances are the person
completing the form could get a little frazzled by it.

So, unless real-time updates of the totals are truly
necessary -- or unless I've missed a more efficient
way -- I'd say you might want to do this without
On-Entry macros and run basically the same code, but
just once, after the form is done -- or at least at
long intervals, say, at the last checkbox in each
section, rather than after every single click.

I don't have time to comment this code, but I think it
should be sort-of-semi-clear.  The colz variable is
probably superfluous now since I'm assuming all tables
have the same number of columns, so the actual column
count (adjusted as shown) could be substituted there.  
Same for the dimension of the Totalz array and the
ta.Columns.Count parameter in the first "For i" loop.

This code assumes the fields for the totals are named
Text1, Text2, Text3, etc.

Hope this helps a little.


"Life is nothing if you aren't obsessed." --John Waters
-------------------------------------------------------

Sub TallyCheckBoxesAtEachClick()
Dim ta As Table, ro As Row, ff As FormField
Dim i As Integer, colz As Integer, Totalz() As Integer

Application.ScreenUpdating = False

colz = ActiveDocument.Tables(1).Columns.Count - 1
ReDim Totalz(colz - 1) As Integer

For Each ta In ActiveDocument.Tables
   For Each ro In ta.Rows
      For i = 2 To ta.Columns.Count
         For Each ff In ro.Cells(i).Range.FormFields
            If ff.Type = wdFieldFormCheckBox Then
               If ff.CheckBox.Value = True Then
                  Totalz(i - 2) = Totalz(i - 2) + 1
               End If
            End If
         Next ff
      Next i
   Next ro
Next ta

If ActiveDocument.ProtectionType = _
   wdAllowOnlyFormFields Then
      ActiveDocument.Unprotect
End If

For i = 2 To Val(colz) + 1
   ActiveDocument.FormFields("Text" & i - 1).Result _
      = Totalz(i - 2)
Next i

ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
    NoReset:=True

Application.ScreenUpdating = True
End Sub

Quote:

> I've just been given a huge Word 97 form used for
> employee performance reviews.

> The powers that be would like Word to sum up the
> form field check boxes in each column, thus
> creating a running total of checks that updates
> whenever you select or deselect a check box.

> I've yet to figure out a simple macro that can be
> applied to the different tables of check boxes in
> this lengthy document.  Could anyone help me out?

> Thanks!

> Leah

> Sent via Deja.com
> http://www.*-*-*.com/



Sun, 27 Jul 2003 17:59:17 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Writing Macro with Check Box Form Field

2. Check for checked values in List Box

3. Check Box being checked if database value is equal to ON

4. Assigning a Value to a Text Box

5. Assigning a value from a list box to a variable

6. Assign value to each letter in text box

7. ~Sum existing values with values from another table

8. ~Sum existing values with values from another table

9. assign value to variable in form module

10. Is it possible to assign a form value to a Session variable in Javascript

11. How to assign value to another form

12. Putting a value from a combo box into a text box on a form

 

 
Powered by phpBB® Forum Software