HELP with checkbox form calculation VBA

[etsutton]

I found a macro online that assigns values to checkboxes in ROWS and puts the total of all checked boxes at the end of the ROW. I have tried to change that to assign values to checkboxes in a COLUMN, with the total appearing at the bottom of that COLUMN, and I am getting an error at the line marked with a * below. PLEASE HELP IF YOU CAN!!


Sub SumTableColCheckBoxes()
Dim ffldsChkBoxes As Word.FormFields
Dim frmField As Word.FormField
Dim CheckBoxValue As Integer
Dim RunningCheckBoxSum As Integer
Dim currCol 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.Columns(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
currCol = Trim(Str(Selection.Cells(1).ColumnIndex))
frmTotalName = "Col" & currCol & "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


:banghead:

[fumei]

Which line?

What error?

[etsutton]

Sorry! Getting "Compile Error: Method or data member not found" on this line, at "Range":

Set ffldsChkBoxes = Selection.Columns(1).Range.FormFields

[gmaxey]

Columns don't have a Range property. You can bookmark the column and use something like:

Option Explicit
Sub SumTableColCheckBoxes()
Dim ffldsChkBoxes As Word.FormFields
Dim frmField As Word.FormField
Dim CheckBoxValue As Integer
Dim RunningCheckBoxSum As Integer
Dim currCol As String
Dim frmTotalName As Variant 'Variant because string not accepted

Application.ScreenUpdating = False
If Selection.Information(wdWithInTable) = True Then
'MsgBox Selection.FormFields(1).Name & vbCr & Selection.FormFields(1).CheckBox.Value
Set ffldsChkBoxes = Selection.Bookmarks(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
currCol = Trim(Str(Selection.Cells(1).ColumnIndex))
frmTotalName = "Col" & currCol & "Total"
MsgBox RunningCheckBoxSum
'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) As Long
GetValueFromName = 1
' Dim ValueStartPos As Integer
' Dim FormFieldValue As Integer
' If InStr(FormFieldName, "VAL") > 0 Then
' ValueStartPos = InStr(FormFieldName, "Val") + 3
' FormFieldValue = Mid(FormFieldName, ValueStartPos)
' End If
' GetValueFromName = FormFieldValue
End Function

[etsutton]

Thank you very much! I have 4 columns next to each other and the values are different for the checkboxes in each column. For example, in Col1 each box=2, in Col2 each box=1, etc... I just tried this with two bookmarked columns side by side and the second column total is including some check values from the first column...
How do I ensure that each column of checkboxes only shows the total for THAT specific column at the bottom in the CheckBoxSum field?

THANK YOU for your BRAIN!

[gmaxey]

This is a bit simplified. Each column is bookmarked Col1, Col2, Col3 etc. There are four columns to process:

Option Explicit
Sub SumTableColCheckBoxes()
Dim ffldsChkBoxes As Word.FormFields
Dim frmField As Word.FormField
Dim CheckBoxValue As Integer
Dim RunningCheckBoxSum As Integer
Dim currCol As String
Dim frmTotalName As Variant 'Variant because string not accepted
Dim i As Long
Application.ScreenUpdating = False
For i = 1 To 4
Set ffldsChkBoxes = ActiveDocument.Bookmarks("Col" & i).Range.FormFields
RunningCheckBoxSum = 0
For Each frmField In ffldsChkBoxes
If frmField.Type = wdFieldFormCheckBox Then
If frmField.CheckBox.Value = True Then
RunningCheckBoxSum = RunningCheckBoxSum + GetValue(i)
End If
End If
Next
currCol = Trim(Str(Selection.Cells(1).ColumnIndex))
frmTotalName = "Col" & currCol & "Total"
MsgBox RunningCheckBoxSum
Next i
'ActiveDocument.FormFields(frmTotalName).Result = RunningCheckBoxSum
End Sub

Function GetValue(lngRouter As Long) As Long
Select Case lngRouter
Case 1
GetValue = 2
Case 2
GetValue = 1
Case 3
GetValue = 3
Case 4
GetValue = 4
End Select
' Dim ValueStartPos As Integer
' Dim FormFieldValue As Integer
' If InStr(FormFieldName, "VAL") > 0 Then
' ValueStartPos = InStr(FormFieldName, "Val") + 3
' FormFieldValue = Mid(FormFieldName, ValueStartPos)
' End If
' GetValueFromName = FormFieldValue
End Function

[etsutton]

THANK YOU again very much for your help! It has really been a life saver. I have one final question. I was able to tweak the last code you sent slightly, since I was actually looking to populate a cell with the column totals rather than display a message. Since my third column will always equal ZERO, I didn't need a macro for that one. That said, it turned out to be easier to include three (3) separate macros - one for each of the other checkbox columns in the table.

The problem is that the document has EIGHT similar tables and I can't duplicate Bookmark names. Do you know of any easy way to have each macro apply to 8 separate occasions rather than write 24 separate macros for the file. I have no problem with 24 macros if that is the best solution. I've included the document, in case it helps, which is unprotected for this purpose. The first table ("Overall") has been completely setup and is working correctly.

Here's the final VBA I ended up with(thanks to your help!):

Sub SumEXCELS()
Dim ffldsChkBoxes As Word.FormFields
Dim frmField As Word.FormField
Dim CheckBoxValue As Integer
Dim RunningCheckBoxSum As Integer
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.Bookmarks("Col5").Range.FormFields
RunningCheckBoxSum = 0
For Each frmField In ffldsChkBoxes
If frmField.Type = wdFieldFormCheckBox Then
If frmField.CheckBox.Value = True Then
CheckBoxValue = 2
RunningCheckBoxSum = RunningCheckBoxSum + CheckBoxValue
End If
End If
Next
ActiveDocument.FormFields("Col2Total").Result = RunningCheckBoxSum
Else
MsgBox "Any checkbox or formfield running this macro must be in a table."
End If
End Sub

Sub SumMEETS()
Dim ffldsChkBoxes As Word.FormFields
Dim frmField As Word.FormField
Dim CheckBoxValue As Integer
Dim RunningCheckBoxSum As Integer
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.Bookmarks("Col3").Range.FormFields
RunningCheckBoxSum = 0
For Each frmField In ffldsChkBoxes
If frmField.Type = wdFieldFormCheckBox Then
If frmField.CheckBox.Value = True Then
CheckBoxValue = 1
RunningCheckBoxSum = RunningCheckBoxSum + CheckBoxValue
End If
End If
Next
ActiveDocument.FormFields("Col3Total").Result = RunningCheckBoxSum
Else
MsgBox "Any checkbox or formfield running this macro must be in a table."
End If
End Sub

Sub SumUNSAT()
Dim ffldsChkBoxes As Word.FormFields
Dim frmField As Word.FormField
Dim CheckBoxValue As Integer
Dim RunningCheckBoxSum As Integer
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.Bookmarks("Col2").Range.FormFields
RunningCheckBoxSum = 0
For Each frmField In ffldsChkBoxes
If frmField.Type = wdFieldFormCheckBox Then
If frmField.CheckBox.Value = True Then
CheckBoxValue = -1
RunningCheckBoxSum = RunningCheckBoxSum + CheckBoxValue
End If
End If
Next
ActiveDocument.FormFields("Col5Total").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
GetValueFromName = FormFieldValue
End Function


THANKS AGAIN!

[gmaxey]

Now that I see what you are actually doing I think you are making it much too complicated.

The following two procedures set to run OnEntry (the first one) and OnExit the second should work:

(Please note there is "ALWAYS" the chance that a user will enter but not exit a formfield. This could result in inaccuracies.) You might consider intercepting the FileSave and FilePrint to ensure that user cannot do either if the focus is on a checkbox.

Option Explicit
Dim m_oColIndex As Long
Sub GetoColIndex()
m_oColIndex = Selection.Information(wdEndOfRangeColumnNumber)
TallyColumn
End Sub
Sub TallyColumn()
Dim oTbl As Word.Table
Dim oTbls As Word.Tables
Dim oCol As Column
Dim oCell As Cell
Dim i As Long
Dim j As Long
Dim frmField As Word.FormField
Dim RunningCheckBoxSum As Long
Dim lngTotal As Long
Application.ScreenUpdating = False
If Selection.Information(wdWithInTable) = True Then
Set oTbl = Selection.Tables(1)
i = m_oColIndex
RunningCheckBoxSum = 0
For j = 2 To oTbl.Rows.Count - 3
Set oCell = oTbl.Cell(j, i)
Set frmField = oCell.Range.FormFields(1)
If frmField.CheckBox.Value = True Then
Select Case i
Case 2
RunningCheckBoxSum = RunningCheckBoxSum + 2
Case 3
RunningCheckBoxSum = RunningCheckBoxSum + 1
Case 4
RunningCheckBoxSum = RunningCheckBoxSum
Case 5
RunningCheckBoxSum = RunningCheckBoxSum - 1
End Select
End If
Next j
If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect
oTbl.Cell(oTbl.Rows.Count - 2, i).Range.Text = RunningCheckBoxSum
oTbl.Cell(oTbl.Rows.Count, 2).Range.Text = Format(Val(oTbl.Cell(oTbl.Rows.Count - 2, 2).Range.Text) _
+ Val(oTbl.Cell(oTbl.Rows.Count - 2, 3).Range.Text) _
+ Val(oTbl.Cell(oTbl.Rows.Count - 2, 5).Range.Text) / 3, "#.00")
Set oTbls = ActiveDocument.Tables
lngTotal = 0
For j = 2 To 9
lngTotal = lngTotal + Val(oTbls(j).Cell(oTbls(j).Rows.Count, 2).Range.Text)
Next j
oTbls(10).Cell(oTbls(j).Rows.Count, 2).Range.Text = lngTotal
ActiveDocument.Protect wdAllowOnlyFormFields, True
Else
MsgBox "Any checkbox or formfield running this macro must be in a table."
End If
End Sub


Get rid of all bookmarks and formfields in the 8 tables except for the checkboxes.

I'm not sure I know how to attach a file but I will if I can.

[etsutton]

This is a much more elegant solution - Thank you! The only problem now is that the math doesn't look right. For some reason it seems to be assigning a value of -.33 to the checkboxes in the final column, instead of -1. I would attempt to fix the VBA myself, but you lost me with the latest (and greatest) update. Would you mind taking one last look at the attached document (v3) to see what might be happening?

Thank you so much again for all your help, Greg!

[gmaxey]

Oops, for some reason I thought you were looking for some sort of average.

You know that users can easily crap this up by checking a box in more than one column? Ideally you would create a userform and validate the entries.

Good luck.

[etsutton]

I was able to figure out the math part in the 'grand total' cell now that you told me you were doing an average before. It was just as simple as taking out the "/3" part in this line (I also allowed for proper formatting of negative numbers):

+ Val(oTbl.Cell(oTbl.Rows.Count - 2, 5).Range.Text), "##;-##")
I cannot THANK YOU enough for your help, seriously, THANK YOU!!

Have a great weekend!
Eric

[gmaxey]

Eric,

You're welcom. As I mentioned in the earlier post, since formfields have no change event there is always the change that data could be entere by the user and not processed. Another issue is there is nothing to stop the user from checking two or more boxes in each assessment area.

I mentioned a userform. I've spent far too much time on the attached than I meant to, but I got started and just couldn't walk away from it.

It works basically like this. Each option button has a hard coded caption "2 2 2, 8 3 4, etc. that represents a table, a row, and a column index. When the form is displayed the caption values are written the the option button .Tag property and the .Caption property is set to ""

When the user selects the option buttons and click the command button then true value reuslt in a checkbox symbol being placed in the targeted table cell, false valuse insert a empty box.

Give it a try and see what you think.

P.S. Change the file to a template (.dotm)

[gmaxey]

Still tinkering (or perhaps piddling). There is no need to have a protected form with Word 2007/2010 you can wrap it all in a rich text content control. This way the employess still have spell check ;-).

I've also modified the form (applied styles etc.). Unless you object, I may polish this a little more and post it as an example template on my web-site.

Good luck.

[etsutton]

I don't see an attachment to your last two replies, but I'd love to check out what you've done. It sounds great. The attachment feature is just below the reply area under "additional options".

I have no objection to you posting the document, but would you mind replacing ALL of the copy with either Greek ("Lorem Ipsem") or your own copy? This copy is owned by my client and I wouldn't want them to throw a fit, or to sue either one of us for copyright infringement. It is especially important that the intro copy on Page 1 and Page 8 and the copy for each of the assessments is changed before this gets posted anywhere else. I'd suggest putting something like "Assessment 1, Assessment 2, etc." in the tables and just Lorem ipsum for the first and last page.

Thanks again for your help on this! You've gone above and beyond.

-Eric

[gmaxey]

Odd that you don't see the attachments as they appear when I look at the forum. Anyway, if you send me a feedback http://gregmaxey.mvps.org/my_web_feedback.aspx I'll reply and send the attachment. Yeah, I would definately make it generic before posting anywhere else.




I don't see an attachment to your last two replies, but I'd love to check out what you've done. It sounds great. The attachment feature is just below the reply area under "additional options".

I have no objection to you posting the document, but would you mind replacing ALL of the copy with either Greek ("Lorem Ipsem") or your own copy? This copy is owned by my client and I wouldn't want them to throw a fit, or to sue either one of us for copyright infringement. It is especially important that the intro copy on Page 1 and Page 8 and the copy for each of the assessments is changed before this gets posted anywhere else. I'd suggest putting something like "Assessment 1, Assessment 2, etc." in the tables and just Lorem ipsum for the first and last page.

Thanks again for your help on this! You've gone above and beyond.

-Eric

[etsutton]

Now I see the attachments! It looks great and works like a charm! Side note: I don't see the little user form man icon in my Mac version of Word - do you know if this would work on a Mac as well? Either way, I would say this is definitely one for the records.

However, like I mentioned in my last reply, please just change the intro copy and maybe re-word each assessment slightly so it isn't an exact replica. I would also feel more comfortable if you took out any "clever" copy such as "Why Are We Doing This?, etc. Anything that reflects the brand identity of my client should come out before you use the form on your site. Other than that, I say GREAT WORK and Godspeed!

Eric

[gmaxey]

OK, good. The icon might have been lost when I had to save template file as a document file and I'm not sure about a Mac or how they work. Anyway, the macro is the same one call with the macrobutton so you can assign it to a keyboard shortcut or whatever.

Yes, sure if I post something on my website is will be to show the technique and it won't look like your form.

Good luck.