Sue said:
I'm designing an evaluation form. On this form there will be
questions which a user has to choose a rating of 1-5. I'd like to
make these check boxes. I would then like to calculate the average
of the 10 or so ratings and display the average at the bottom of the
form. I'm not a VB person. If at all possible I'd like to do it
through the Toolbox. Can someone help? I've attempted to set all
the individual check boxes up seperatly, but it's allot of work and I
can't find a way to give them any value except true or false.
Thanks!
Last things first: Forget about the Control Toolbox. Those things are
ActiveX controls, which are memory-hungry. If you stick 50 or more of them
in a document, it'll run like molasses if it doesn't crash outright. Use the
Forms toolbar checkboxes and a macro. Here's how...
To get numeric values for a series of checkboxes, create a table in your
template, and put one checkbox in each cell except for a row of labels
across the top and a column of labels down the left side. If the box in
column 2 is checked, the value of the row is 1; if the box in column 3 is
checked the value is 2, and so on.
The macro below is in charge of figuring this out, and also making sure that
each row has one and only one box checked. Follow the directions at
http://www.gmayor.com/installing_macro.htm to paste it into your template.
At the bottom of the template, outside the table, put a text form field
named "Average" and set its Entry macro to be this CalculateAverage macro.
Protect the template for forms (click the lock icon on the toolbar) and
save, making sure the "Save as type" box at the bottom of the dialog says
"Word Template". Then use File > New to make a document based on the
template, and try it out. When you click in the Average field, if each row
has one box checked, the average of the row values will appear in the field.
I know you said you aren't a VB person, but I hope the comments (the lines
that start with an apostrophe) will help you understand what's going on in
this macro. Reply and ask about anything that isn't clear.
Public Sub CalculateAverage()
Dim oTbl As Table
Dim nRow As Long, nCol As Long
Dim nMaxRow As Long, nMaxCol As Long
Dim nTotal As Long
Dim sAverage As Single
Dim bOneChecked As Boolean
' Get the table and its size
Set oTbl = ActiveDocument.Tables(1)
nMaxRow = oTbl.Rows.Count
nMaxCol = oTbl.Columns.Count
' The code assumes the table has exactly one
' label row across the top and exactly one
' label column down the left side. These cells
' are excluded from the computation.
' First verify that each row has exactly
' one box checked. To do this, look at the
' value of the checkbox in each cell along
' the row, using the bOneChecked variable
' to track whether a True box has already
' been found.
For nRow = 2 To nMaxRow
bOneChecked = False
For nCol = 2 To nMaxCol
If oTbl.Cell(nRow, nCol).Range _
.FormFields(1).CheckBox.Value Then
' the checkbox in cell (nRow, nCol) is True
If bOneChecked Then
' this is the second True in this row,
' so complain, select the extra box, and
' then exit the macro
MsgBox "Question " _
& nRow - 1 _
& ": More than one box checked"
oTbl.Cell(nRow, nCol).Range _
.FormFields(1).Range.Select
Set oTbl = Nothing
Exit Sub
Else
' this is the first True in this row, so
' set the variable and continue the loop
bOneChecked = True
End If
End If
Next
If Not bOneChecked Then
' didn't find any True checkboxes
' in this row, so complain, select the first box
' and then exit the macro
MsgBox "Question " & nRow - 1 _
& ": No boxes checked"
oTbl.Cell(nRow, 1).Range _
.FormFields(1).Range.Select
Set oTbl = Nothing
Exit Sub
End If
Next
' If we get here, table is ok.
' Compute the total of all rows. The numeric value
' of each row is the column number of the True
' checkbox, minus 1 to allow for the label column.
nTotal = 0
For nRow = 2 To nMaxRow
For nCol = 2 To nMaxCol
If oTbl.Cell(nRow, nCol).Range _
.FormFields(1).CheckBox.Value Then
nTotal = nTotal + (nCol - 1)
End If
Next
Next
' Compute the average. The number of items is
' the number of rows minus 1 to allow for the
' label row.
sAverage = CSng(nTotal) / CSng(nMaxRow - 1)
' Place the average, rounded to 2 decimal places,
' in the text form field.
ActiveDocument.FormFields("Average").Result = _
Format(sAverage, "0.00")
Set oTbl = Nothing
End Sub
--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
