Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Mar 2012
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts

    How to tabulate check box entries

    Hi,

    I sorely need help to tabulate results of checkbox form I created, basically 3 choices of Yes, No and N/A in 3 indivual columns. I need to know count for each checked answer.

    I've attached the file.

    Thanks!

    Jun
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    Jun,

    Welcome to the Lounge!

    This can be done, but it will take a little work to set up:
    First, double-click on each 'Yes' checkbox, and set the Bookmark name as follows: "chkY1", "chkY2", "chkY3" and so on, all the way down the Yes column.
    Then do the same for the No, and N/A columns. Name the 'No' checkboxes "chkN1", "chkN2" etc., and name the "N/A" checkboxes "chkA1", "chkA2" etc.

    Once that has been set up, you can tabulate the results of the checkboxes by running the following macro code:
    Code:
    Sub TabulateCheckboxes()
       Dim y As Long
       Dim n As Long
       Dim a As Long
       Dim ff As FormField
       
       For Each ff In ActiveDocument.FormFields
          If ff.Type = wdFieldFormCheckBox Then
             Select Case Left$(ff.Name, 4)
                Case "chkY"
                   If ff.Result = True Then
                      y = y + 1
                   End If
                Case "chkN"
                   If ff.Result = True Then
                      n = n + 1
                   End If
                Case "chkA"
                   If ff.Result = True Then
                      a = a + 1
                   End If
             End Select
          End If
       Next 'ff
       MsgBox "There are " & CStr(y) & " Yes checkboxes, " _
                          & vbCrLf & CStr(n) & " No checkboxes, and " _
                          & vbCrLf & CStr(a) & " N/A checkboxes."
    End Sub
    By the way, in actual use, are you protecting the form before you have users fill it out?

    Gary

  3. The Following User Says Thank You to Gary Frieder For This Useful Post:

    justjun (2012-03-19)

  4. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,899
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Rather than using multiple Checkbox formfields, I'd recommend using Dropdown formfields, with blank, Yes, No & N/A options. It's then an easy matter to use a formula field to tally how many Yes, No & N/A options have been chosen. A side benefit is that your form would also then have more space for user input in the other columns.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. The Following User Says Thank You to macropod For This Useful Post:

    justjun (2012-03-19)

  6. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,515
    Thanks
    3
    Thanked 143 Times in 136 Posts
    I second Paul's suggestion, the other problem with the form as posted is that the user could check more than one box per line - how would you count those lines?

    The alternative is to use Option buttons with groups so that each line has three mutually exclusive options.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    justjun (2012-03-19)

  8. #5
    New Lounger
    Join Date
    Mar 2012
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks, guys, I'll play around with the suggestions and see which solution is a best fit.

    And Gary, to answer your question, the form will be protected with the exception of the last 5 columns (for user input).

    Thanks again! I hope you guys don't mind if I come around again when I hit a wall. Cheers!

  9. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    Just want to note that I agree with Paul and Andrew's suggestions, that having three checkboxes is not the best way to go.

    Gary

  10. #7
    New Lounger
    Join Date
    Mar 2012
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I guess I can't even export the checkboxes to Excel as the "checks" won't show. The idea is to send this form to various programs areas to populate. Once all the forms are completed and received back (by me), I will need to create a table summarizing all the responses by all programs areas so that at a glance one can see who answered Y, N and N/A to what item.

    An option is to create this in Excel but inputting long-winded texts (even with wrap text on) is just not possible. Or is it? I am lost.... :-(

  11. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,899
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Hi justjun,

    Once you have the data, it's easy enough to extract it from the Word document and export it to Excel. You'd probably want to wait till all the responses are in, then batch process the lot using a macro.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #9
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    Quote Originally Posted by justjun View Post
    An option is to create this in Excel but inputting long-winded texts (even with wrap text on) is just not possible. Or is it? I am lost.... :-(
    How lengthy are the text entries that get entered? If they're not too long, then creating this as a protected Excel form is certainly also an option. You can produce the equivalent of a Word forms drop-down list, using an Excel data validation list, and you can use formulas to tabulate the number of Yes, No, N/A entries in a given column.

    Gary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •