Assign Numeric Value to Check Box & Add to Total if Checked

[dham63]

Good Day,

I am using word 2003. I have a table 3 columns across with each column holding a check box. The column headings are Good, Bad and Not Applicable. If a user clicks good I want a numeric value of 10 assigned, if they click bad I want a numeric value of 5 assigned, If they click Not Applicable I want a value of 0 assigned. I want to add these numbers up and divide by the number of rows where Good or Bad is checked to get an average rating. I have thought that putting in a text box that totals the numbers, another text box that counts the row where there is a number other than 0 and a third text box dividing the two to get my rating. I am a little stuck as to the actual macros though and haven't seen any postings for something similar. Any help is greatly appreciated.

 

[SkipVought]

Hi,

Any reason you are using MS Word for this application? It does not seem to be the best tool for your stated needs. This could be done in Excel very easily.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dham63]

Hi, yes it needs to be in Word. It is one aspect of a much larger project to which this rating table it related, and is all in Word.

 

[SkipVought]

Can you embed a spreadsheet? Spreadsheet formulas are much more robust than formulas in Word. Averaging the non-zero responses is a snap with SUMIF and COUNTIF. Probably would not need any VBA.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dham63]

While I realize it might be easier in excel, the table is already designed in word and redesigning the entire in excel would probably take as much work as learning how to apply the vba in word. So my first preference is not to embed an excel. This is new functionality I want to apply to a document already designed and in use.

 

[SkipVought]

"each column holding a check box"

What kind of check box -- Forms or Control Toolbox?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dham63]

They are form fields

 

[DaveInIowa]

Try this:

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 good As Integer
    Dim bad 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
                            good = good + 1
                            col = 4 'Ignore other checked box(es) on this row
                        Case 2
                            bad = bad + 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 = (good * 10) + (bad * 5)
    ThisDocument.FormFields("Text2").Result = good + bad
    
    If (good + bad = 0) Then
        ThisDocument.FormFields("Text3").Result = "N/A"
    Else
        ThisDocument.FormFields("Text3").Result = ((good * 10) + (bad * 5)) / (good + bad)
    End If
    
End Sub

 

[fumei]

The real point is...assigned to what? A variable I assume.

This can be done in Word, but you need to fully expand the logic.

Row1 Good (check) Bad (uncheck) N/A (uncheck)
Row2 Good (uncheck) Bad (uncheck) N/A (check)
Row3 Good (uncheck) Bad (check) N/A (uncheck)
Row4 Good (check) Bad (check) N/A (check)
Row5 Good (uncheck) Bad (uncheck) N/A (uncheck)
Row6 Good (check) Bad (uncheck) N/A (uncheck)


Look at the above. Notice a few things.

1. Row4 has ALL of them checked. As you are using formfields, this is very possible. There is nothing to stop it.

Now what?

2. Row5 has them ALL unchecked. Also possible. Now what?

3. Row6 has TWO checked....ummmm....now what?

Further, say each row did have only one checked.

Row1 Good (check) Bad (uncheck) N/A (uncheck)
Row2 Good (uncheck) Bad (uncheck) N/A (check)
Row3 Good (uncheck) Bad (check) N/A (uncheck)
Row4 Good (check) Bad (uncheck) N/A (uncheck)
Row5 Good (uncheck) Bad (uncheck) N/A (check)
Row6 Good (check) Bad (uncheck) N/A (uncheck)

According to what you posted, numerically, you end up with:

Row1 = 10
Row2 = 0
Row3 = 5
Row4 = 10
Row5 = 0
Row6 = 10

"I want to add these numbers up and divide by the number of rows where Good or Bad is checked to get an average rating."

OK, let's do that.

Adding up: 35
Number of rows where Good or Bad checked: 4

"divide by the number of rows where Good or Bad is checked to get an average rating."

35 / 4 = 8.75

I fail to see how this is meaningful.

There are a number of routes to getting the values out, and in can be done in Word. You just need to determine, precisely, what the logic is.

It would MUCH easier if the checkboxes are named intelligently. Something like:

All the Good checkboxes named: Good_1, Good_2, Good_3...
All the Bad checkboxes named: Bad_1, Bad_2, Bad_3...
All the N/A checkboxes named: NA_1, NA_2, NA_3...

Then you can access the data (True or False) of the checkboxes by type (Good, Bad, NA), using the .Name property of the formfield.

What I do not understand is if the row itself is significant.

If it really is, then I would go even farther in explicitly naming.

R1_Good, R1_Bad, R1_NA
R2_Good, R2_Bad, R2_NA

Now you could extract both row information (R1) and Good/Bad/NA from the name, and its value (True or False).

Again though, as these are formfields, you CAN have both Good and Bad checked.

Bottom line though, this is a straightforward logic issue. Figure out the exact logic required and it can be done.

faq219-2884

Gerry
My paintings and sculpture

 

[dham63]

DaveInIowa - this did exactly what I needed, thank you so much

 

[fumei]

I see Dave got in before me...after I posted. This is a good approach.

faq219-2884

Gerry
My paintings and sculpture