Word Table Calculation VBA

[TomorrowsMan]

Okay, I think I'm getting there; thanks for all your help out there...

So here's where I stand: I have my rows of check boxes in a table; I
need the user to be able to select only one choice [ed.: can't use
buttons in a userform...don't ask, but it was forbidden) per row, which
will be totalled (then multiplied by 60%) later.

The check box groups successfully limit the user to one per row; and
the total calculates for the first row. BUT, that's where I hit the
wall:

When I check a box in the second row, it changes the total in the first
row. GRR! I know it must have to do with them being in groups [as
required by the first bit of code, but likely interfering with the
second], and I'm not sure how to track down where the flaw is.

Here's what I've got so far; the top half I got from you folks, the
rest I found as noted:


Sub SumTableRowCheckBoxes()

' CheckExclusive Macro

Dim oField As FormField

For Each oField In Selection.Frames(1).Range.FormFields
oField.CheckBox.Value = False
Next oField

' FormField Value Macro
' from http://homepage.swissonline.ch/cindymeister/formfaq.htm

Selection.FormFields(1).CheckBox.Value = True
Dim ffldsChkBoxes As Word.FormFields
Dim frmField As Word.FormField
Dim CheckBoxValue As Integer
Dim RunningCheckBoxSum As Integer
Dim currRow As String
Dim frmTotalName As Variant 'Variant because string not accepted

ScreenUpdating = False
If Selection.Information(wdWithInTable) = True Then
'MsgBox Selection.FormFields(1).Name & vbCr &
Selection.FormFields(1).CheckBox.Value
Set ffldsChkBoxes = Selection.Rows(1).Range.FormFields
RunningCheckBoxSum = 0
For Each frmField In ffldsChkBoxes
If frmField.Type = wdFieldFormCheckBox Then
If frmField.CheckBox.Value = True Then
CheckBoxValue = 0
CheckBoxValue = GetValueFromName(frmField.Name)
RunningCheckBoxSum = RunningCheckBoxSum +
CheckBoxValue
End If
End If
Next
currRow = Trim(Str(Selection.Cells(1).RowIndex))
frmTotalName = "Row" & currRow & "Total"
ActiveDocument.FormFields(frmTotalName).Result =
RunningCheckBoxSum
Else
MsgBox "Any checkbox or formfield running this macro must be in
a table."
End If
End Sub

Function GetValueFromName(FormFieldName As Variant)
Dim ValueStartPos As Integer
Dim FormFieldValue As Integer
If InStr(FormFieldName, "Val") Then
ValueStartPos = InStr(FormFieldName, "Val") + 3
FormFieldValue = Mid(FormFieldName, ValueStartPos)
End If
GetValueFromName = FormFieldValue
End Function


I'm so close to getting this thing wrapped up, which will make me very
happy because I can then go back to my much easier Excel and Acrobat
files...but they insist this one remain in Word.

Cheers,
TMan

 

[Doug Robbins - Word MVP]

I can't tell exactly what you have, but if you have a table with check boxes
in all columns except the last column and in the last column you have a
textbox formfield (assumed to be given the bookmark names row1, row2, row 3,
etc.) with data entry to those formfields not being enabled, and then you
have a final row in the table in which you have a calculation type formfield
that calculates the sum of the values of the other textbox formfields
(=row1+row2+row3, etc) and you have the following macro run on exit from
each of the checkbox formfields and you have the calculate on exit property
for those formfields set, the textbox formfield at the end of each row will
be updated with the column number of the checked box and the textbox
formfield in the final row will be updated with the sum of the column
numbers:

Dim i As Long, j As Long, k As Long
i = Selection.Information(wdEndOfRangeRowNumber)
j = Selection.Information(wdEndOfRangeColumnNumber)
With Selection.Tables(1).Rows(i)
If .Cells(j).Range.FormFields(1).CheckBox.Value = True Then
For k = 1 To .Cells.Count - 1
If k <> j Then
.Cells(k).Range.FormFields(1).CheckBox.Value = False
End If
Next
.Cells(.Cells.Count).Range.FormFields(1).result = j
End If
End With

The user must tab out of the cells for this to work.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Okay, I think I'm getting there; thanks for all your help out there...

So here's where I stand: I have my rows of check boxes in a table; I
need the user to be able to select only one choice [ed.: can't use
buttons in a userform...don't ask, but it was forbidden) per row, which
will be totalled (then multiplied by 60%) later.

The check box groups successfully limit the user to one per row; and
the total calculates for the first row. BUT, that's where I hit the
wall:

When I check a box in the second row, it changes the total in the first
row. GRR! I know it must have to do with them being in groups [as
required by the first bit of code, but likely interfering with the
second], and I'm not sure how to track down where the flaw is.

Here's what I've got so far; the top half I got from you folks, the
rest I found as noted:


Sub SumTableRowCheckBoxes()

' CheckExclusive Macro

Dim oField As FormField

For Each oField In Selection.Frames(1).Range.FormFields
oField.CheckBox.Value = False
Next oField

' FormField Value Macro
' from http://homepage.swissonline.ch/cindymeister/formfaq.htm

Selection.FormFields(1).CheckBox.Value = True
Dim ffldsChkBoxes As Word.FormFields
Dim frmField As Word.FormField
Dim CheckBoxValue As Integer
Dim RunningCheckBoxSum As Integer
Dim currRow As String
Dim frmTotalName As Variant 'Variant because string not accepted

ScreenUpdating = False
If Selection.Information(wdWithInTable) = True Then
'MsgBox Selection.FormFields(1).Name & vbCr &
Selection.FormFields(1).CheckBox.Value
Set ffldsChkBoxes = Selection.Rows(1).Range.FormFields
RunningCheckBoxSum = 0
For Each frmField In ffldsChkBoxes
If frmField.Type = wdFieldFormCheckBox Then
If frmField.CheckBox.Value = True Then
CheckBoxValue = 0
CheckBoxValue = GetValueFromName(frmField.Name)
RunningCheckBoxSum = RunningCheckBoxSum +
CheckBoxValue
End If
End If
Next
currRow = Trim(Str(Selection.Cells(1).RowIndex))
frmTotalName = "Row" & currRow & "Total"
ActiveDocument.FormFields(frmTotalName).Result =
RunningCheckBoxSum
Else
MsgBox "Any checkbox or formfield running this macro must be in
a table."
End If
End Sub

Function GetValueFromName(FormFieldName As Variant)
Dim ValueStartPos As Integer
Dim FormFieldValue As Integer
If InStr(FormFieldName, "Val") Then
ValueStartPos = InStr(FormFieldName, "Val") + 3
FormFieldValue = Mid(FormFieldName, ValueStartPos)
End If
GetValueFromName = FormFieldValue
End Function


I'm so close to getting this thing wrapped up, which will make me very
happy because I can then go back to my much easier Excel and Acrobat
files...but they insist this one remain in Word.

Cheers,
TMan