Count Checkmarks

[Greg Maxey]

Hi,

Yesterday I created and posted the following crude macro to someone looking
for a way to add up the choices made in a list. The idea being a table with
a column of questions and three columns headed Yes, No, and Maybe. For each
question there is a checkbox in the headed columns. The user needs to tally
the number of checked boxes in each column and display the result in the
final row:

Sub Countboxes()

Dim Yes As Long
Dim No As Long
Dim Maybe As Long
With ActiveDocument
If .FormFields("Yes1").Result = True Then
Yes = Yes + 1
End If
If .FormFields("Yes2").Result = True Then
Yes = Yes + 1
End If
'Repeat adding the above If string for each Yes checkbox.
If .FormFields("No1").Result = True Then
No = No + 1
End If
If .FormFields("No2").Result = True Then
No = No + 1
End If
If .FormFields("Maybe1").Result = True Then
Maybe = Maybe + 1
End If
If .FormFields("Maybe2").Result = True Then
Maybe = Maybe + 1
End If
ActiveDocument.FormFields("Text1").Result = Yes
ActiveDocument.FormFields("Text2").Result = No
ActiveDocument.FormFields("Text3").Result = Maybe

End With
End Sub

For this macro to work, each formfield check box must be named and there
must be three lines of code for each checkbox. OK as is for a small list,
but if it was of any size it is soon too laborious to pursue.

Question. Is it possible to write this something like:
dim FF as formfield
For each FF.type (checkbox) in column "Yes" If result = True Then
Yes = Yes + 1
next FF
For each FF.type(checkbox) in column "NO" If result = True Then
No = No +1
etc.?

Thanks.


--
\\\\///////////
( @ @ )
----oo00---(_)---00oo----
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL

 

[JGM]

Hi Greg,

I worked out two options, one slow and one "automatic". But I do not know if
the second option is sound programming!

1)

Create the table and assign a bookmark to each of the column containing the
checkboxes.
In the total row, add a disabled text field in each cell under each column
(Where the totals will be)
Then use/adapt/play with the following code:
________________________________________________________
Sub Main()

Dim Count_Yes As Long
Dim Count_No As Long
Dim Count_Maybe As Long

Application.ScreenUpdating = False
System.Cursor = wdCursorWait
Application.StatusBar = "Please wait, currently tallying up the
checkmarks..."

Count_Yes = Count_Mark("Range_Yes")
Count_No = Count_Mark("Range_No")
Count_Maybe = Count_Mark("Range_Maybe")

Insert_Total "Total_Yes", Count_Yes
Insert_Total "Total_No", Count_No
Insert_Total "Total_Maybe", Count_Maybe

Application.ScreenRefresh
Application.ScreenUpdating = True
Application.StatusBar = "Done!"

End Sub
____________________________________________
Function Count_Mark(oBookMark As String) As Long

Dim oRange As Range
Dim ff As FormField
Dim oCount As Long

Set oRange = ActiveDocument.Bookmarks(oBookMark).Range

oCount = 0

For Each ff In oRange.FormFields
If ff.Result = True Then
oCount = oCount + 1
End If
Next ff

Count_Mark = oCount

Set oRange = Nothing

End Function
______________________________________________
Sub Insert_Total(oFormField As String, oTotal As Long)

ActiveDocument.FormFields(oFormField).Result = oTotal

End Sub
______________________________________________
Finally, create a button on a custom toolbar that calls the Main sub above.
It works but it is slow to tally up if you have long columns. The problem is
the fact that it takes a long time to do the For each... Next, so you cannot
have it as an exit macro for the checkboxes, the user would go crazy! But at
the end, as a one time deal... why not? Also, you could add it to the Print
or Close event to make sure the totals are always up to date...

For the second option, I used an Entry and Exit macro for each checkbox
(Plus calculate on exit), I have a disabled text field for each of the
totals at the bottom of the columns, and I use global boolean variables.
This is where I do not know if it is sound programming. My limited testing
worked fine, but.... Can you store a value in a boolean variable during the
entry level macro, and then retrieve its value during the exit event?
in any case , here are the six macros and the global variables I used:
Public oYes As Boolean
Public oNo As Boolean
Public oMaybe As Boolean
______________________________________________
Sub Enter_Total_Yes()

oYes = Selection.FormFields(1).Result

End Sub

______________________________________________
Sub Enter_Total_No()

oNo = Selection.FormFields(1).Result

End Sub

______________________________________________
Sub Enter_Total_Maybe()

oMaybe = Selection.FormFields(1).Result

End Sub

______________________________________________
Sub Change_Total_Yes()

Dim oTotal

oTotal = ActiveDocument.FormFields("Tally_Yes").Result

If Selection.FormFields(1).Result = oYes Then
Exit Sub
Else
If oYes = False Then
oTotal = oTotal + 1
Else
If Not oTotal = 0 Then 'to avoid negative numbers
oTotal = oTotal - 1
End If
End If
End If

ActiveDocument.FormFields("Tally_Yes").Result = oTotal

End Sub

______________________________________________
Sub Change_Total_No()

Dim oTotal

oTotal = ActiveDocument.FormFields("Tally_No").Result

If Selection.FormFields(1).Result = oNo Then
Exit Sub
Else
If oNo = False Then
oTotal = oTotal + 1
Else
If Not oTotal = 0 Then 'to avoid negative numbers
oTotal = oTotal - 1
End If
End If
End If

ActiveDocument.FormFields("Tally_No").Result = oTotal

End Sub

______________________________________________
Sub Change_Total_Maybe()

Dim oTotal

oTotal = ActiveDocument.FormFields("Tally_Maybe").Result

If Selection.FormFields(1).Result = oMaybe Then
Exit Sub
Else
If oMaybe = False Then
oTotal = oTotal + 1
Else
If Not oTotal = 0 Then 'to avoid negative numbers
oTotal = oTotal - 1
End If
End If
End If

ActiveDocument.FormFields("Tally_Maybe").Result = oTotal

End Sub

______________________________________________
Another problem with the second option is that if the user clicks directly
in the checkbox, the Entry macro is not activated (There ar no standard
OnClick events for checkboxes in Word). So the user has to use the Tab key
to enter the checkboxes... unless I missed something!

So, all in all, the first option is slower but safer!

--
_________________________________________

Jean-Guy Marcil
(e-mail address removed)

Cheers!

 

[Greg Maxey]

Jean-Guy,

I read through your proposed solutions. I don't know if I am up to figuring
out how to make either of them work (e.g., I don't know what a disabled text
field is among other things), but I will play around with it this weekend.
Thanks.

--
\\\\///////////
( @ @ )
----oo00---(_)---00oo----
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL

 

[Mark Tangard]

Greg,

By 'disabled text field' he probably means a text formfield with
the 'Fill-In Enabled' box unchecked.

 

[Greg]

Mark,

That makes sense. Thanks for the tip. Now I will have
try and figure out if I can make it work.

 

[Doug Robbins - Word MVP]

Hi Greg,

Assuming that the first row of the table is used from column headings and
the first column for the questions, the Yes check box is in the second
column, No in the third and Maybe in the fourth, use

Dim Yes As Integer, No As Integer, Maybe As Integer, i As Integer
Yes = 0
No = 0
Maybe = 0
For i = 2 To ActiveDocument.Tables(1).Rows.Count
Yes = Yes - ActiveDocument.Tables(1).Cell(i,
2).Range.FormFields(1).CheckBox.Value
No = No - ActiveDocument.Tables(1).Cell(i,
3).Range.FormFields(1).CheckBox.Value
Maybe = Maybe - ActiveDocument.Tables(1).Cell(i,
4).Range.FormFields(1).CheckBox.Value
Next i
MsgBox Yes
MsgBox No
MsgBox Maybe

If a box is checked, it's value is - 1, hence subtracting the negative
values from zero gives a positive result.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[JGM]

That's right Mark,

Sorry, I have a French version of XP, so I translate as I go along. I have
an English version of Word on another machine, I guess I should not be so
lazy and check it out for proper English terms...

Sorry Greg if my stuff is difficult to understand... I am just a beginner
who saw your post as a challenge, and it reminded me of something I had done
before... So I gave it a go!

I am sure there is a better way of doing this...

Good luck!

 

[JGM]

Hi Doug,

Ha! I knew there was a better way of doing this!

say Doug, I tried your code and modified it slightly as follows:
_________________________________________
Sub Main()

Dim Yes As Integer, No As Integer, Maybe As Integer, i As Integer
Yes = 0
No = 0
Maybe = 0
For i = 2 To ActiveDocument.Tables(1).Rows.Count - 1
Yes = Yes - ActiveDocument.Tables(1).Cell(i, 2).Range. _
FormFields(1).CheckBox.Value
No = No - ActiveDocument.Tables(1).Cell(i, 3).Range. _
FormFields(1).CheckBox.Value
Maybe = Maybe - ActiveDocument.Tables(1).Cell(i, 4).Range. _
FormFields(1).CheckBox.Value
Next i
ActiveDocument.FormFields("YesTotal").Result = Yes
ActiveDocument.FormFields("NoTotal").Result = No
ActiveDocument.FormFields("MaybeTotal").Result = Maybe

End Sub
_________________________________________

Then I added this macro to the On exit "event" for each checkbox, added one
text field for the total of each column and I unchecked the 'Fill-In
Enabled' box for those total fields.
My question is this:
How long can the table be before it becomes annoying for the user to wait
for the macro to finish running before moving on to the next checkbox
(Assuming a PIII machine as a reference, kind of fast, but not the fastest
machine around!) everytime a check is added or removed?
Or,
For "live" purposes, what are the limits of the For ... Next syntax?

Thanks.

 

[Greg]

Hi Doug and Jean-Guy,

I started working with Doug's method first and Jean-Guy I
am happy you jumped in and stated it was a better way. Now
I won't have to face potential failure understanding your
method.

I immediately started looking for a way to display the
results in the table vice the msg boxes but was getting a
error until I created a new single row table below the one
I was working to hold the results. Jean-Guy your little "-
1" addition solved the error and now I have the results in
the same table and understand what was causing the error.

Rather than run the macro on exit from each checkbox, I
just put a macrobutton in the first column with a prompt
to double click for results.

This works perfectly. Thanks to both of you for your
quick responses and willingness to share yor substantial
talents.

Greg