Microsoft Office Forums Assigning Values to content control checkboxes and calculating results

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-19-2012, 06:30 PM
creative cathy creative cathy is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Novice
Assigning Values to content control checkboxes and calculating results
 
Join Date: Apr 2012
Posts: 6
creative cathy is on a distinguished road
Cool Assigning Values to content control checkboxes and calculating results

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?
Reply With Quote
  #2  
Old 04-21-2012, 04:02 AM
macropod's Avatar
macropod macropod is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi Cathy,

What kinds of checkboxes are you using (Content Control, Formfield, ActiveX)?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 04-21-2012, 05:44 PM
creative cathy creative cathy is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Novice
Assigning Values to content control checkboxes and calculating results
 
Join Date: Apr 2012
Posts: 6
creative cathy is on a distinguished road
Default

Paul - I am using Content Control (from the Developer Tab)
CC
Reply With Quote
  #4  
Old 04-22-2012, 02:55 PM
macropod's Avatar
macropod macropod is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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:
Code:
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
__________________
Cheers,
Paul Edstein
[MS MVP - Word]

Last edited by macropod; 04-22-2012 at 03:12 PM. Reason: Simplified code
Reply With Quote
  #5  
Old 04-27-2012, 10:34 AM
creative cathy creative cathy is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Novice
Assigning Values to content control checkboxes and calculating results
 
Join Date: Apr 2012
Posts: 6
creative cathy is on a distinguished road
Default I am helpless!

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
Attached Files
File Type: docx PerformanceReview.docx (49.9 KB, 61 views)
Reply With Quote
  #6  
Old 04-27-2012, 04:37 PM
macropod's Avatar
macropod macropod is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
Attached Files
File Type: zip PerformanceReview.zip (55.5 KB, 136 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 04-30-2012, 04:02 PM
creative cathy creative cathy is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Novice
Assigning Values to content control checkboxes and calculating results
 
Join Date: Apr 2012
Posts: 6
creative cathy is on a distinguished road
Default

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?
Reply With Quote
  #8  
Old 04-30-2012, 04:59 PM
macropod's Avatar
macropod macropod is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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 ...
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 05-01-2012, 11:23 AM
creative cathy creative cathy is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Novice
Assigning Values to content control checkboxes and calculating results
 
Join Date: Apr 2012
Posts: 6
creative cathy is on a distinguished road
Default

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?
Reply With Quote
  #10  
Old 05-01-2012, 03:30 PM
macropod's Avatar
macropod macropod is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi Cathy,

That can overcome with a few code changes. See attached.
Attached Files
File Type: zip PerformanceReview.zip (81.0 KB, 248 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #11  
Old 05-01-2012, 03:45 PM
creative cathy creative cathy is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Novice
Assigning Values to content control checkboxes and calculating results
 
Join Date: Apr 2012
Posts: 6
creative cathy is on a distinguished road
Default

Paul -
You are the best ! Thanks so much for all the help!
Cheers!
Cathy
Reply With Quote
  #12  
Old 10-07-2012, 06:36 PM
sleepytulog sleepytulog is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Novice
 
Join Date: Oct 2012
Posts: 2
sleepytulog is on a distinguished road
Default What if....

How will I get it to work if there's an NA=0 option as well?
Reply With Quote
  #13  
Old 10-07-2012, 07:51 PM
macropod's Avatar
macropod macropod is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #14  
Old 10-07-2012, 08:52 PM
sleepytulog sleepytulog is offline Assigning Values to content control checkboxes and calculating results Windows 7 64bit Assigning Values to content control checkboxes and calculating results Office 2010 32bit
Novice
 
Join Date: Oct 2012
Posts: 2
sleepytulog is on a distinguished road
Default

Thanks so much, you sir, are awesome
Reply With Quote
Reply

Tags
checked boxes, formulas

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Assigning Values to content control checkboxes and calculating results Calculating values from Drop Down Selections bbucher Word 15 04-09-2017 08:53 PM
Assigning Values to content control checkboxes and calculating results Assigning values to formfield checkboxes in a table mammiano Word Tables 13 01-12-2015 06:27 AM
Deleting a table from a content control -- preserving the content control BrainSlugs83 Word Tables 8 11-14-2013 03:06 AM
Assigning Values to content control checkboxes and calculating results Content control merge values? skrallemand Word VBA 8 10-02-2013 06:54 AM
Calendar control accepts other values JeJ Word 0 03-02-2011 03:38 PM


All times are GMT -7. The time now is 05:35 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft