
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/