Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / February 2008

Tip: Looking for answers? Try searching our database.

Checkbox calculations in form

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
J C - 01 Feb 2008 16:01 GMT
Hi, I am working on a form with mulitple checkboxes and was wondering how can
I count the amount of checkboxes then calculate a percent checked?

Any help is appreciated.  Thanks.

JC
Jay Freedman - 01 Feb 2008 17:53 GMT
>Hi, I am working on a form with mulitple checkboxes and was wondering how can
>I count the amount of checkboxes then calculate a percent checked?
>
>Any help is appreciated.  Thanks.
>
>JC

Sub CheckboxPercent()
   Dim TotalCheckBoxes As Long
   Dim CheckedBoxes As Long
   Dim Pct As Single
   Dim FFld As FormField
   Dim myDoc As Document
   
   Set myDoc = ActiveDocument ' or another
   
   TotalCheckBoxes = 4
   ' or call CheckboxCount like this:
   ' TotalCheckBoxes = CheckboxCount(myDoc)
   CheckedBoxes = 0
   
   For Each FFld In myDoc.FormFields
       If FFld.Type = wdFieldFormCheckBox Then
           If FFld.CheckBox.Value = True Then
               CheckedBoxes = CheckedBoxes + 1
           End If
       End If
   Next
   
   Pct = 100# * CSng(CheckedBoxes) / CSng(TotalCheckBoxes)
   
   MsgBox Pct & "% of the checkboxes are checked."
End Sub

' for use if you don't know in advance how many
' checkboxes there are in the document
Function CheckboxCount(myDoc As Document) As Long
   Dim FFld As FormField
   Dim Ct As Long
   Ct = 0
   For Each FFld In myDoc.FormFields
       If FFld.Type = wdFieldFormCheckBox Then
           Ct = Ct + 1
       End If
   Next
   
   CheckboxCount = Ct
End Function

--
Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.
J C - 01 Feb 2008 19:40 GMT
Hi Jay, I should've have been a bit more specific.  This form has multiple
sections with different field types (including checkboxes), and for each
section is a "Completed" checkbox for that section.  So I wouldn't want this
script to count all of the checkboxes but only these "Completed" checkboxes.  
How can this script be modified to include only the section "Completed"
checkboxes?  If there is a better way of doing so I am all ears.

Thanks,
JC

> >Hi, I am working on a form with mulitple checkboxes and was wondering how can
> >I count the amount of checkboxes then calculate a percent checked?
[quoted text clipped - 50 lines]
> Microsoft Word MVP        FAQ: http://word.mvps.org
> Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.
Jay Freedman - 02 Feb 2008 00:43 GMT
>Hi Jay, I should've have been a bit more specific.  This form has multiple
>sections with different field types (including checkboxes), and for each
>section is a "Completed" checkbox for that section.  So I wouldn't want this
>script to count all of the checkboxes but only these "Completed" checkboxes.  
>How can this script be modified to include only the section "Completed"
>checkboxes?  If there is a better way of doing so I am all ears.

This can be done, but you have to tell the macro how to recognize which ones are
the "Completed" checkboxes. Do they have something specific in their names that
aren't in any other checkbox's name? Or are they formatted with a specific style
that isn't used anywhere else? Or some other unique characteristic that a macro
could use to say "this one should be counted, but that one shouldn't"?

If you don't have any such characteristic yet, probably the easiest one is to
change the names of the "Completed" boxes. When the form is unprotected,
double-click one of these checkboxes to open its Options dialog and change the
entry in the "Bookmark" box. For example, you could name them "Completed1",
"Completed2", etc. where the number is the section number.

Then the For loop in the macro can be rewritten like this:

   For i = 1 To TotalCheckBoxes
       Set FFld = myDoc.FormFields("Completed" & i)
       If FFld.CheckBox.Value = True Then
           CheckedBoxes = CheckedBoxes + 1
       End If
   Next i

This can be rather touchy -- if the value of TotalCheckBoxes is wrong, or if you
don't have a checkbox name for each value of i between 1 and TotalCheckBoxes,
then an error will stop the macro. It's possible to add error checking to catch
these problems; I didn't show it here to keep things simple.

Another approach is to use the For Each as in the original macro, and use a
different If statement in place of "If FFld.Type = wdFieldFormCheckBox Then" to
make sure the name is "Completed" and a number:

 If FFld.Name Like "Completed#" Then

Make the same replacement in the CheckboxCount function if you use that.

--
Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.
J C - 04 Feb 2008 15:49 GMT
I tried to combine your recommendations into this but it doesn't work.  For
this line entry "Set FFld = myDoc.FormFields("Check" & i)" I get an "Compile
error: Invalid or unqualified reference".  Please be with me as this is the
first time I tried to do anything fancy in Word...

--------------------------------------------
Sub CheckBoxPercent()
   Dim TotalCheckBoxes As Long
   Dim CheckedBoxes As Long
   Dim Pct As Single
   Dim FFld As FormField
   Dim myDoc As Document
   
   Set myDoc = ActiveDocument
   
   TotalCheckBoxes = 22
   
   For i = 1 To TotalCheckBoxes
       Set FFld = myDoc.FormFields("Check" & i)
       If FFld.CheckBox.Value = True Then
           CheckedBoxes = CheckedBoxes + 1
       End If
   Next i
   
   Pct = 100# * CSng(CheckedBoxes) / CSng(TotalCheckBoxes)
   
   .FormFields("Percentage").Result = Pct

End Sub
--------------------------------------------------------

> >Hi Jay, I should've have been a bit more specific.  This form has multiple
> >sections with different field types (including checkboxes), and for each
[quoted text clipped - 42 lines]
> Microsoft Word MVP        FAQ: http://word.mvps.org
> Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.
Jamie - 21 Feb 2008 21:31 GMT
Hi Jay, I am trying to use the coding you supplied to JC, but I’m not sure
how to tweek it to fit my scenario.

I’m working in Word 2003, I have a table set up (15 rows).  I have a column
titled Intake.  The fields in this column are check boxes (1 for each row).  
I have given the check boxes in this column a new bookmark name, Intake
(e.g., Intake1, Intake2 all the way to Intake15).  Like JC, I just want to
count how many checkboxes have been checked.  I do not need to calculate a
percentage as JC did.  My coding is as follows:

Sub IntakeCount()
 Dim TotalCheckBoxes as Long
 Dim CheckedBoxes as Long
 Dim FFld as FormField
 Dim myDoc as Document

 Set myDoc = ActiveDocument

 For i = 1 To TotalCheckBoxes
     Set FFld = myDoc.FormFields(“Intake” & i)
     If FFld.CheckBox.Value = True Then
          CheckedBoxes = CheckedBoxes + 1
     End If
 Next i

I have the macro run On Entry in formfield, Text108.  Nothing happens.  I
can’t figure out what I’m missing.  Any help you can give me would be
wonderful!  Thanks

Signature

Jamie

> >Hi Jay, I should've have been a bit more specific.  This form has multiple
> >sections with different field types (including checkboxes), and for each
[quoted text clipped - 42 lines]
> Microsoft Word MVP        FAQ: http://word.mvps.org
> Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.
Doug Robbins - Word MVP - 21 Feb 2008 23:22 GMT
Easiest way is the run a macro with the following code, adjusting the table
index and number of the cell as required

Dim i as Long, result as Long
result = 0
With ActiveDocument
   With .Tables(1)
       For i = 1 to 15
           If .Cell(i, 1).Range.FormFields(1).CheckBox.Value = True Then
               result = result + 1
           End if
       Next i
   End With
   .Formfields("Text108").Result = result
End With

Signature

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

> Hi Jay, I am trying to use the coding you supplied to JC, but I'm not sure
> how to tweek it to fit my scenario.
[quoted text clipped - 89 lines]
>> Email cannot be acknowledged; please post all follow-ups to the newsgroup
>> so all may benefit.
Jamie - 21 Feb 2008 23:39 GMT
Hi Doug, I get an error message for this line

   If .Cell(i, 1).Range.FormFields(1).CheckBox.Value = True Then

The error says - The requested member of the collection does not exist.
Signature

Jamie

> Easiest way is the run a macro with the following code, adjusting the table
> index and number of the cell as required
[quoted text clipped - 105 lines]
> >> Email cannot be acknowledged; please post all follow-ups to the newsgroup
> >> so all may benefit.
Jamie - 21 Feb 2008 23:51 GMT
Hi Doug, ignore my last posting.  I figured out the problem, the column with
the checkboxes is column 5, so I replaced the 1 with 5 and it is working.  
Thanks so much for your help!
Signature

Jamie

> Hi Doug, I get an error message for this line
>
[quoted text clipped - 111 lines]
> > >> Email cannot be acknowledged; please post all follow-ups to the newsgroup
> > >> so all may benefit.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.