Assigning Values to content control checkboxes and calculating results

[creative cathy]

In Word 2010 I have created a table.
The table has several rows of check boxes each 5 columns wide.
Each box in each column represents a score 1 -5 (1 being worst, 5 being best)
I would like to take the value for the checked box in each row and add them together then divide them by the number of rows to come up with an average score to be displayed in single cell on a seperate row.


Can I do this?
Also - along the same line, can I prevent the user from checking more than one box per row?

[macropod]

Hi Cathy,

What kinds of checkboxes are you using (Content Control, Formfield, ActiveX)?

[creative cathy]

Paul - I am using Content Control (from the Developer Tab)
CC

[macropod]

Hi Cathy,

Your document will need some vba code to do what you want. Try adding the following code to your document's 'ThisDocument' module:

Option Explicit
Dim oTbl As Table, oCel As Cell
Dim lRow As Long, lCol As Long, lColIdx As Long
Dim lRowCount As Long, lCtlCount As Long
 
Private Sub Document_ContentControlOnEnter(ByVal ContentControl As ContentControl)
With Selection
  If .Information(wdWithInTable) Then
    Set oTbl = .Tables(1)
    lCol = .Cells(1).ColumnIndex
    lRow = .Cells(1).RowIndex
  End If
End With
End Sub
 
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
If oTbl = Nothing Then Exit Sub
With oTbl
  If ContentControl.Type = wdContentControlCheckBox Then
    If ContentControl.Checked = True Then
      For Each oCel In .Rows(lRow).Cells
        With oCel.Range
          If .ContentControls.Count > 0 Then
            If .ContentControls(1).Type = wdContentControlCheckBox Then
              If oCel.ColumnIndex <> lCol Then
                If .ContentControls(1).Checked = True Then lColIdx = oCel.ColumnIndex
                .ContentControls(1).Checked = False
              End If
            End If
          End If
        End With
      Next
    End If
  End If
  Call Tally
End With
Set oTbl = Nothing
End Sub
 
Private Sub Tally()
Application.ScreenUpdating = False
With oTbl
  For Each oCel In .Columns(lCol).Cells
    With oCel.Range
      If .ContentControls.Count > 0 Then
        If .ContentControls(1).Type = wdContentControlCheckBox Then
          lRowCount = lRowCount + 1
          If .ContentControls(1).Checked = True Then lCtlCount = lCtlCount + 1
        End If
      End If
    End With
  Next
  With .Columns(lCol)
    If lRowCount > 0 Then .Cells(.Cells.Count).Range.Text = (lCtlCount / lRowCount) * .Index
  End With
  lRowCount = 0: lCtlCount = 0
  If lColIdx > 0 Then
    For Each oCel In oTbl.Columns(lColIdx).Cells
      With oCel.Range
        If .ContentControls.Count > 0 Then
          If .ContentControls(1).Type = wdContentControlCheckBox Then
            lRowCount = lRowCount + 1
            If .ContentControls(1).Checked = True Then lCtlCount = lCtlCount + 1
          End If
        End If
      End With
    Next
    With .Columns(lColIdx)
      .Cells(.Cells.Count).Range.Text = (lCtlCount / lRowCount) * .Index
    End With
  End If
  lRowCount = 0: lCtlCount = 0: lColIdx = 0: lCol = 0: lRow = 0
End With
Application.ScreenUpdating = True
End Sub

To see how to install the macros, go to: http://www.gmayor.com/installing_macro.htm

[creative cathy]

Thank you for the code -
Doesn't seem to be working for me...can you review and see what I am doing wrong? I have attached File. I could not attach it as a macro enabled file.
Thanks

[macropod]

hi Cathy,

To attach a docm file, zip it first (as I have done). See attached update.

Apart from some code changes, I've split your table. That's because you have essentially three sets of data to tally and this is the simplest way to handle them. It doesn't materially affect your document's layout, though.

[creative cathy]

Hopefully my last question. The macro works great! Thanks! But I noticed that it disabled the rows marked employee information from repeating on each page. Not a problem - but I at least need each footer to to have a field that automatically shows the employee name on each page. How do I create this custom footer field?

[macropod]

Hi Cathy,

What you can do is:
• Bookmark the employee information table (eg EmployeeInfo)
• Change your page layout to 'different first page' (via Page Layout|Page Setup|Layout)
• Insert a cross-reference to the 'EmployeeInfo' bookmark in the second page's header.

If you prefer to have just the employee's name in the footer:
• Apply a custom character Style to the employee'n name content control (eg EmployeeInfo)
• Change your page layout to 'different first page' (via Page Layout|Page Setup|Layout)
• Insert a STYLEREF field pointing to the 'EmployeeInfo' Stylein the second page's footer.

You can even do both ...

[creative cathy]

The issue seems to be this, the employee info area is a form. I can bookmark it and make it a header but only the form appears and if I fill in the information on the original form it does not show up on the subsequent pages only blanks appear.
The same seems to be true of crossreferencing it and putting it in a footer. If I do so, the information within the form's field does not seem to appear.
For example, I would like to take the employee's name after is is filled in on the form and add it to each subsequent pages footnote - but it only show up as a blank as it is on the field before the form is filled in.
It is the same with adding the rows included in Employy Information, I can bookmark them sucessfully and it appears as a new header but only in blank form. If I fill out the original form in the Bookmark, it does not populate the information on the subsequent pages.
Any thoughts?

[macropod]

Hi Cathy,

That can overcome with a few code changes. See attached.

[creative cathy]

Paul -
You are the best ! Thanks so much for all the help!
Cheers!
Cathy

[sleepytulog]

How will I get it to work if there's an NA=0 option as well?

[macropod]

This thread is getting a bit old now, so I don't recall the details with pefect clarity.

That said, basically, you'd either change one of the column headers or you'd add another column. If you do the latter, you'd probably need to change all the 5s in the macros to 6s.

[sleepytulog]

Thanks so much, you sir, are awesome