Calculate Checkbox Value - Word

[hahnsm]

hahnsm (MIS) Jan 26, 2007
I am working on a Word document (survey) that has 5 sections. Each section has a 5 questions that will be
answered using a scale from 1 to 5 and group of checkboxes to represent 1 to 5 for each question. How can I assign a value to each checkbox so that I can calculate the average for each section? I currently have the checkboxes in a table in Word.Questions are in column A, CheckBoxes in column B, and I would like the return value in Column C. I have considered creating a userform if this would make the coding easier.
Thanks!
Hahnsm

 

[fumei]

1. Are these FormField checkboxes (using the Forms toolbar), or ActiveX checkboxes (using the Controls toolbar)?

2. When you say assign a value, do you mean something like:

Define a variable value, and

If Checked, value = value + 1
If Unchecked, value = value + 0

3. In your table, is the structure like:

Row 1 Col 1 Question Col 2 5 checkboxes
Row 2 Col 1 Question Col 2 5 checkboxes
Row 3 Col 1 Question Col 2 5 checkboxes
Row 4 Col 1 Question Col 2 5 checkboxes
Row 5 Col 1 Question Col 2 5 checkboxes

4. If there are 5 rows (one for each question), and 5 checkboxes per question, WHAT is to go into Col 3? There are five column 3.

5. IMO, a userform would in fact be better.

Gerry
My paintings and sculpture

 

[hahnsm]

Thanks for your response Gerry!

To answer your questions -

1. The checkboxes are from the Form Toolbar

2. Out of the 5 check boxes that are listed, the user is only going to select 1 of those boxes. I would like to write code (this is the hard part for me) that yes something like -
If (Checkbox Name)"Section1Checkbox1Point1" is true, give me a value of 1 in C1, If "Section1Checkbox2Point2" is true, give me a value of 2 in C1, If "Section1Checkbox3Point3" is true, give me a value of 3 in C1 . . . . and so on until the end of all 5 checkboxes and then end my IF Statement

3. Yes - this is my table structure

4. Column 3 or C in my example above would include the Rating Value of the check boxes

5. Is it possible to do this with what I have, with the table in the document?

Thanks for your help!!

Sadie

 

[Andrzejek]

If you use checkboxes, user can check more than one. That's the way checkboxes work. Do you want that?

Option buttons (radio buttons) would be a better choice, IMHO

Have fun.

---- Andy

 

[hahnsm]

That is a very good idea Andy. Thanks for the advice. I will switch to the option buttons. Is it possible to create code that can return a value based on which buttonn is selected (1-5)??

Sadie

 

[Andrzejek]

Sure, why not.

I use VB6 and do not know much about VBA, just enough to survive in Word and Excel.

You do know that each group of option buttons will have to be placed in its own container.

To bad you can not use control arrays in VBA (or can you?)

I still don't know what you mean by 'value of a check box'. For me is either True or False.


Have fun.

---- Andy

 

[macropod]

Hi ,

Your initial question was

How can I assign a value to each checkbox so that I can calculate the average for each section?

but later you describe getting a count, not an average. To get an *average* you could use code like:

Sub AverageChecks()
Dim oFld As FormField
Dim i As Integer
Dim j As Integer
Dim k As Integer
With ActiveDocument
    For i = 1 To .Sections.Count
        j = 0
        k = 0
        For Each oFld In .Sections(i).Range.FormFields
            If oFld.Type = wdFieldFormCheckBox Then
                k = k + 1
                If oFld.Result = True Then j = j + 1
            End If
            .FormFields("Average" & i).Result = j / k
        Next oFld
    Next i
End With
End Sub

The above code puts the *averages* into formfields named 'Average1', Average2' etc - one for each Section in the document. If you want *counts* make the following changes to the code:
Delete: Dim k As Integer
Delete: k = 0
Delete: k = k + 1
Change: .FormFields("Average" & i).Result = j / k to .FormFields("Count" & i).Result = j
and send the output to formfields named 'Count1', Count2' etc.

Yes, one could use table references, but this could end up requiring more work if someone adds another table or three later on, or if someone decides that the results shouldn't be in a table.

As per my post in the Office Forum, it would seem to me to be far simpler to use a dropdown field (but with values 1-5) to set the score for each item. It would then be a relatively straightforward matter to use a formula field to calculate the averages.

Cheers

[MS MVP - Word]