Reference original thread707-1519663
DaveinIowa had a good solution several years ago for a user who had a similar problem that I do. I am preparing a form that has to be in Word (2007 or 2010) that will contain a table with four columns. Columns 2-4 continue check boxes (however, only one row has a column 4, the remainders only have 3 columns). Simply stated, this is a faculty evaluation form that is replacing a previously created online complete and print PDF form. We no longer have the capability or the original person who created those forms to handle the update.
Column 1 is a description of the evaluation category. Column 2 contains a checkbox for YES, Column 3 contains a checkbox for NO, and in the single row (12) that has a cell in Column 4 it contains a checkbox for N/A. All YES values should equal 4 and all NO values should equal 1. The original form calculated an average of all values and placed it in a smaller text box in the upper right corner of the form. This value also read through to a master aggregating form in PDF format. We can manually insert the value from the Word version of the redesigned form, which isn't a problem.
I'm not particularly good with macros or VBA, somewhat rusty after about 13 years. Here's what I got from the original thread listed above as posted by DaveinIowa (modified values to fit my circumstances):
Public Sub thread707_1519663()
Dim tb As Table
Dim rw As Row
Dim rg As Range
Dim ff As FormField
Dim col As Integer
Dim yes As Integer
Dim no As Integer
Dim na As Integer
' Set tb to the table with the check boxes ...
Set tb = ThisDocument.Tables(1)
For Each rw In tb.Rows
Set rg = rw.Range
col = 0
For Each ff In rg.FormFields
If (ff.Type = wdFieldFormCheckBox) Then
col = col + 1
If (ff.CheckBox.Value = True) Then
Select Case col
Case 1
yes = yes + 1
col = 4 'Ignore other checked box(es) on this row
Case 2
no = no + 1
col = 4 'Ignore other checked box(es) on this row
Case 3
na = na + 1
End Select
End If
End If
Next
Next
ThisDocument.FormFields("Text1").Result = (yes * 4) + (no * 1)
ThisDocument.FormFields("Text2").Result = yes + no
If (yes + no = 0) Then
ThisDocument.FormFields("Text3").Result = "N/A"
Else
ThisDocument.FormFields("Text3").Result = ((yes * 4) + (no * 1)) / (yes + no)
End If
End Sub
What my problem is involves WHERE does the calculation go if the above code works? I want it in the small box described above. How would I do that? I tried naming the box "Text3" but that didn't work, nor after protecting the template did it allow me to update the totals in that box. The other problem involves prevention of checking more than one box on a line. The original PDF version had radio buttons which, if a change was made, erased the previous choice on the same line. Would that work better than check boxes?
Appreciate any assistance anyone can provide. I do have the macro enabled template available that I can send anyone who would like to see it.
Thanks very much!
DaveinIowa had a good solution several years ago for a user who had a similar problem that I do. I am preparing a form that has to be in Word (2007 or 2010) that will contain a table with four columns. Columns 2-4 continue check boxes (however, only one row has a column 4, the remainders only have 3 columns). Simply stated, this is a faculty evaluation form that is replacing a previously created online complete and print PDF form. We no longer have the capability or the original person who created those forms to handle the update.
Column 1 is a description of the evaluation category. Column 2 contains a checkbox for YES, Column 3 contains a checkbox for NO, and in the single row (12) that has a cell in Column 4 it contains a checkbox for N/A. All YES values should equal 4 and all NO values should equal 1. The original form calculated an average of all values and placed it in a smaller text box in the upper right corner of the form. This value also read through to a master aggregating form in PDF format. We can manually insert the value from the Word version of the redesigned form, which isn't a problem.
I'm not particularly good with macros or VBA, somewhat rusty after about 13 years. Here's what I got from the original thread listed above as posted by DaveinIowa (modified values to fit my circumstances):
Public Sub thread707_1519663()
Dim tb As Table
Dim rw As Row
Dim rg As Range
Dim ff As FormField
Dim col As Integer
Dim yes As Integer
Dim no As Integer
Dim na As Integer
' Set tb to the table with the check boxes ...
Set tb = ThisDocument.Tables(1)
For Each rw In tb.Rows
Set rg = rw.Range
col = 0
For Each ff In rg.FormFields
If (ff.Type = wdFieldFormCheckBox) Then
col = col + 1
If (ff.CheckBox.Value = True) Then
Select Case col
Case 1
yes = yes + 1
col = 4 'Ignore other checked box(es) on this row
Case 2
no = no + 1
col = 4 'Ignore other checked box(es) on this row
Case 3
na = na + 1
End Select
End If
End If
Next
Next
ThisDocument.FormFields("Text1").Result = (yes * 4) + (no * 1)
ThisDocument.FormFields("Text2").Result = yes + no
If (yes + no = 0) Then
ThisDocument.FormFields("Text3").Result = "N/A"
Else
ThisDocument.FormFields("Text3").Result = ((yes * 4) + (no * 1)) / (yes + no)
End If
End Sub
What my problem is involves WHERE does the calculation go if the above code works? I want it in the small box described above. How would I do that? I tried naming the box "Text3" but that didn't work, nor after protecting the template did it allow me to update the totals in that box. The other problem involves prevention of checking more than one box on a line. The original PDF version had radio buttons which, if a change was made, erased the previous choice on the same line. Would that work better than check boxes?
Appreciate any assistance anyone can provide. I do have the macro enabled template available that I can send anyone who would like to see it.
Thanks very much!
