count the value of Optionbox in a table

[frogman]

I have a table with 5 columns
Step Action Expected Results Pass Fail

in the pass fail columns there are option boxes. I want to count the
number of passes and the number of fails to total them with out counted
every cell. Hopefully there is a loop i can use.

 

[Jonathan West]

I have a table with 5 columns
Step Action Expected Results Pass Fail

in the pass fail columns there are option boxes. I want to count the
number of passes and the number of fails to total them with out counted
every cell. Hopefully there is a loop i can use.

The following code assumes that you put an X in the relevant cells to
indicate a pass or a fail

Dim oCol As Column
Dim oCell As Cell
Dim iPass As Long
Dim iFail As Long

'Count the passes
For Each oCell In ActiveDocument.Tables(1).Columns(4).Cells
If Asc(oCell.Range.Text) = Asc("X") Then
iPass = iPass + 1
End If
Next

'Count the fails
For Each oCell In ActiveDocument.Tables(1).Columns(5).Cells
If Asc(oCell.Range.Text) = Asc("X") Then
iFail = iFail + 1
End If
Next
MsgBox iPass & " passes" & vbCr & iFail & " fails"


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[frogman]

I got that to work the reason I wanted to use Optionsboxes is that I
want to total the fields if one of the pass fail it changed.

 

[Greg Maxey]

Frogman,

Thanks to some great detective work by Jay Freedman, I think I can
offer some help.
If you name all of your Pass optionbuttons Pass1, Pass2, etc. and the
Fail buttons Fail1, Fail 2, etc. then you could get a count of the
active buttons of each type using something like:


Sub CountSelectedOptionBoxes()
Dim ctl As InlineShape
Dim i As Double
Dim j As Double
For Each ctl In ActiveDocument.InlineShapes
If ctl.Type = wdInlineShapeOLEControlObject Then
If InStr(ctl.OLEFormat.Object.Name, "Pass") > 0 Then
i = i + ctl.OLEFormat.Object.Value
ElseIf InStr(ctl.OLEFormat.Object.Name, "Fail") > 0 Then
j = j + ctl.OLEFormat.Object.Value
End If
End If
Next
MsgBox "Pass: " & -i & "Fail: " & -j
End Sub

 

[frogman]

Thanks that works like a charm.

The only thing i had to change was the names of my pass fail labels.
Just a note you can only have the Pass Fail keywords in the objects you
want to try to count and no where else.

On to big and better things