Counting different checkboxes within a table, for varying number of tables

[ghostrider]

Hi folks,

I have a word document with test sheets in the form of tables. I want to automate the calculation of tests complete and compile a list of text entered by the user

I am trying to have a VBA script which will be able to:
1. Detect how many tables of a specific size are in the document
2. Count the number of different checkboxes ticked in a certain cells within each table
3. Extract user inputted text from another certain cell within each table (if another checkbox is ticked) and have the text expressed in a different, list of text results, part of the document
4. Have the heading of each table change colour depending on whether a checkbox is ticked

The key is scalability. The code should continue to work if tables are added/removed by the user - which would likely be done by copying the last table in the document and pasting on the next page.

Understand there are plenty examples of counting checkboxes etc. but I cannot work this out specifically for counting different checkboxes in different cells and have user inputted text repeat automatically in a set different place in the Doc :help.

Thanks

[gmaxey]

What have your tried? 1 and 2 can be worked out something like



Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oTbl As Table
Dim lngIndex As Long, lngChecked As Long
Dim oCell As Cell
Dim oCC As ContentControl
For Each oTbl In ActiveDocument.Tables
If oTbl.Range.Cells.Count = 9 Then
If oTbl.Rows.Count = 3 And oTbl.Columns.Count = 3 Then
lngIndex = lngIndex + 1
End If
End If
Next oTbl
MsgBox "There are " & lngIndex & " (3 x 3) tables (9 cells in this document)"
For Each oTbl In ActiveDocument.Tables
lngChecked = 0
If oTbl.Range.Cells.Count = 9 Then
If oTbl.Rows.Count = 3 And oTbl.Columns.Count = 3 Then
Set oCell = oTbl.Cell(2, 2)
For Each oCC In oCell.Range.ContentControls
If oCC.Type = 8 Then
If oCC.Checked Then lngChecked = lngChecked + 1
End If
Next oCC
MsgBox lngChecked & " checkboxes are checked in this table."
End If
End If
Next oTbl
lbl_Exit:
Exit Sub
End Sub

[ghostrider]

Hi Greg,

Thanks for that - I'm beginning to understand the coding sequence. This code executes fine but the "total boxes checked" returns 0

I've simplified the code a bit to try and get the basics right but still the message box returns "0 checkboxes checked". My word document has a simple 2x2 table just now with checkboxes in each cell checked.

:


Sub ScratchMacro()
Dim oTbl As Table
Dim lngTests As Long, lngChecked As Long, lngBoxes
Dim oCell As Cell
Dim oCC As ContentControl

For Each oTbl In ActiveDocument.Tables
'If oTbl.Range.Cells.Count = 9 Then
'If oTbl.Rows.Count = 3 And oTbl.Columns.Count = 3 Then
lngTests = lngTests + 1
'End If
'End If
Next oTbl
MsgBox "There are " & lngTests & " tests in this document"

For Each oTbl In ActiveDocument.Tables
lngChecked = 0
lngBoxes = 0
'If oTbl.Range.Cells.Count = 9 Then
'If oTbl.Rows.Count = 3 And oTbl.Columns.Count = 3 Then
Set oCell = oTbl.Cell(1, 1)
For Each oCC In oCell.Range.ContentControls
If oCC.Type = 8 Then lngBoxes = lngBoxes + 1
If oCC.Checked = True Then lngChecked = lngChecked + 1
Next oCC
'End If
' End If
Next oTbl
MsgBox lngBoxes & " checkboxes are in this table."
MsgBox lngChecked & " checkboxes are checked in this table."
lbl_Exit:
Exit Sub
End Sub

[gmaxey]

What does ...
MsgBox lngBoxes & " checkboxes are in this table."
... return?

Are you using Content Control checkboxes or some other type?

[ghostrider]

Ah I was using Active X checkboxes - having switched to Check Box Content Control, the code now works!

Thanks Greg

[gmaxey]

Good. Post what you have tried with 3 and 4 if you can't work it out and additional help will be forthcoming if necessary.

[ghostrider]

Good. Post what you have tried with 3 and 4 if you can't work it out and additional help will be forthcoming if necessary.

Sure - I've got a bit working for automatically copying user inputted text from one part of the document to another.

While looping through all ContentControls within a certain cell (oTbl.Cell(1,1)) - if a text field is found, and it doesnt contain the default text, then copy that text from table A to table B in the document.


If (oCC.Type = 1) And Not (oCC.Range.Text = "Click or tap here to enter text.") Then ' if there's text
ActiveDocument.Tables(1).Cell(2, 2).Range.Text = oCC.Range.Text
ActiveDocument.Tables(1).Cell(2, 1).Range.Text = oTbl.Cell(1, 1).Range.Text

MsgBox oCC.Range.Text & ""
End If


The next thing would be to have the number of rows counted in table A (so something like intRows = oTbl.Rows.Count maybe?) and then have the code insert rows in table B to account for if the user wishes to add more text rows in Table A.

For changing the header colour, I think there will be someway like
Set oTbl.Paragraph.Styles = Heading1 or similar

Question: how do I make it so that the text which is copied from Table A to Table B appears as a hyperlink? The idea being that the user can click on the link in Summary Table B and be taken directly to Detailed Table A

[gmaxey]

Something like this could work:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oCellLink As Cell, oCellTarget As Cell
Dim oRng As Range, oBM As Bookmark
Set oCellLink = ActiveDocument.Tables(1).Cell(1, 1)
Set oCellTarget = ActiveDocument.Tables(2).Cell(1, 2)
Set oRng = oCellTarget.Range
oRng.End = oRng.End - 1
Set oBM = ActiveDocument.Bookmarks.Add("SomeName", oRng)
Set oRng = oCellLink.Range
oRng.End = oRng.End - 1
ActiveDocument.Hyperlinks.Add Anchor:=oRng, Address:="", _
SubAddress:=oBM.Name, ScreenTip:="", TextToDisplay:=oRng.Text
lbl_Exit:
Exit Sub

End Sub