Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-05-2021, 11:18 AM
Yeezus Yeezus is offline Macro command to count checkboxes and provide metrics Windows 10 Macro command to count checkboxes and provide metrics Office 2016
Novice
Macro command to count checkboxes and provide metrics
 
Join Date: Aug 2021
Posts: 1
Yeezus is on a distinguished road
Default Macro command to count checkboxes and provide metrics

I have set up the developer option in the ribbon and seem to be using ActiveX controls

Five total columns that are all strings of data with the checkbox as column 1. Do have an overview that starts the table for about three rows or so. Would like to be able to show certain metrics in that area at the top of document

Number of total checkboxes + checkboxes checked
Percentage of checked / unchecked


Am I able to export results to text document? Is it possible to have multiple word document results all be stored in one master results list?

Any insight or information would be much appreciated

Below was probably best results of info when researching:

Quote:
Assuming you have checkboxes in columns (one per cell), and no other type of formfields in the table, you could use ---

Dim pTable As Word.Table
Dim pCount() As Long
Dim pFormField As Word.FormField
Dim pColumn As Long

'Get a reference to the table we're working with
Set pTable = Selection.Tables(1)

'Dimension the array to store the column counts
ReDim pCount(1 To pTable.Columns.Count)

'Iterate the formfields in the table
For Each pFormField In pTable.Range.FormFields

'If checked, increment the count for the column containing this
formfield
If pFormField.CheckBox.Value Then
pColumn = pFormField.Range.Cells(1).ColumnIndex
pCount(pColumn) = pCount(pColumn) + 1
End If

Next

'Create/update the DocVariable for each column
For pColumn = 1 to ubound(pCount)
ActiveDocument.Variables("ColCount_" & pColumn) = pCount(pColumn)
Next

'Update fields to display the new counts
with ActiveDocument
if .ProtectionType wdNoProtection then
.Unprotect
end if
.Fields.Update
.Protect wdAllowOnlyFormFields, noReset:=True
end with


Use DocVariable fields to display the totals {DocVariable ColCount_1} etc.
If the table might contain other types of formfields, you'd need to check
the FormField.Type property. The DocVariable naming convention won't work if
you have more than one table that you want to run this on. You could use the
Table.ID property to set a unique value for each table, and include that in
the DocVariable names.
Reply With Quote
  #2  
Old 08-05-2021, 04:01 PM
Guessed's Avatar
Guessed Guessed is offline Macro command to count checkboxes and provide metrics Windows 10 Macro command to count checkboxes and provide metrics Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

I think you should be using Content Controls for the checkboxes and the reporting fields.

This macro goes into the ThisDocument module
Code:
Private Sub Document_ContentControlOnExit(ByVal thisCC As ContentControl, Cancel As Boolean)
  Dim aCC As ContentControl, iTotal As Integer, iChecked As Integer, dPercent As Double
  If thisCC.Type = wdContentControlCheckBox Then
    iTotal = ActiveDocument.SelectContentControlsByTag("Check").Count
    For Each aCC In ActiveDocument.SelectContentControlsByTag("Check")
      If aCC.Checked Then iChecked = iChecked + 1
    Next aCC
    ActiveDocument.SelectContentControlsByTitle("Number Checked")(1).Range.Text = iChecked
    ActiveDocument.SelectContentControlsByTitle("Number Total")(1).Range.Text = iTotal
    ActiveDocument.SelectContentControlsByTitle("Percentage Checked")(1).Range.Text = Format(iChecked / iTotal, "0%")
  End If
End Sub
Have a look at the attached document as a demo. Check out the properties for each Content Control as they are important for the macro to run correctly.
Attached Files
File Type: docm Summary.docm (31.3 KB, 12 views)
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add / Sum checked Checkboxes labelled 1, 2, 3, etc. in Word Table - Need Macro? klearazkrystal Word VBA 7 03-31-2019 11:50 PM
Need Macro to select Checkbox after two boxes are checked in a series of checkboxes rsaini Word VBA 0 01-16-2018 11:57 AM
count selected checkboxes in each group a888 Word VBA 8 02-24-2015 04:44 AM
Ammunition count macro Owastell Excel Programming 3 07-03-2014 11:00 AM
Collecting Metrics in Project TimJimO Project 0 12-29-2010 12:12 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:44 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft